Creating tables in postgres DB using plpgsql fucntion

Question

Using pgAdmin III, I want to create tables in my postgres DB using a PL/pgSQL function specifically. The following set of commands does the job fine for me.

Create or Replace Function mk_tbl()
Returns Integer As $$
Declare
Begin
Drop Table if exists sel_streets;
Create Table sel_streets
(
id Integer,
Geom geometry
);
Create Index sel_streets_indx
ON sel_streets Using
gist (geom);
Return (1);
End $$ Language plpgsql volatile;

I am calling the function by this command:

Select mk_tbl();

My problem is every time I call this function, it creates tables in postgres DB as well as it returns '1' (in SQL editor output panel) which is not desired. I am aware that ultimately a function should return something and I can also use void construct like this:

Create or Replace Function mk_tbl()
Returns Void As $$
...

However, what I want is to call this function which should only create tables in my potgres DB and should return nothing. Is it possible to do this using PL/pgSQL specifically?

What you are looking for are "real" stored procedures. Unfortunately, Postgres does not support them, so you'll have to live with the returns void (Unrelated: the language name is an identifier, don't put it in single quotes: language plpgsql should be used). — user330315
– user330315, Commented Jun 17, 2016 at 9:03
I'm not sure if perform does not help you - do $$ begin perform mk_tbl(); end;$$; — Vao Tsun
– Vao Tsun, Commented Jun 17, 2016 at 9:05
@a_horse_with_no_name: Thank you for the correction. I have edited the code. Secondly, if I call this function using PL/Python or PL/R then is it possible to do this? — khajlk
– khajlk, Commented Jun 17, 2016 at 9:27
postgresql.org/docs/current/static/plpgsql-statements.html - "...executes query and discards the result..." — Vao Tsun
– Vao Tsun, Commented Jun 17, 2016 at 9:33

Laurenz Albe · Accepted Answer · 2016-06-17 10:15:31Z

1

What is the problem with an empty result?

Using a DO statement would do what you want:

DO LANGUAGE plpgsql $$BEGIN
   PERFORM mk_tbl();
END;$$;

edited Jun 17, 2016 at 10:15

answered Jun 17, 2016 at 10:04

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

khajlk Over a year ago

Well, thanks for your feedback and there is no problem with an empty result. But, it is good to run the query and discard the results. Similarly, I want to create table but without returning anything.

khajlk Over a year ago

Perhaps you may want to update your answer about how to use this block of code. Either inside PLpgSQL or standalone. That would be more helpful for me to mark this as a valid answer because I am still unable to do what is required.

Laurenz Albe Over a year ago

The statement in my answer is an SQL statement, you can run it like any other SQL statement. Running a CREATE TABLE statement never has a result.

Collectives™ on Stack Overflow

Creating tables in postgres DB using plpgsql fucntion

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related