PostgreSQL: Executing dynamic command using EXECUTE .. INTO

Question

I need to create a function which should returns a select statement result from it.

Example:

Create or replace function fun_test(cola text,colb text,rel text)
returns table(columna text,columnb text)as 
$Body$
Declare
      table_name varchar :='Table_';
      temp_t record;
Begin
      table_name := table_name || rel;
      raise info '%',table_name;

             execute 'select distinct'||quote_ident(cola)||','||quote_ident(colb)|| ' from '||quote_ident(table_name) into temp_t; 

             return query select * from temp_t; /* Error relation "temp_t" does not exist

            /* Here I need to update temp_t also*/

end;
$Body$
language plpgsql;

Error:

ERROR:  relation "temp_t" does not exist
LINE 1: select * from temp_t
                      ^

You can't SELECT FROM from a RECORD variable. What are you trying to achieve here? You can't do anything after you RETURN QUERY, that exits the procedure. None of this makes any sense. Have you read the examples in the documentation? — Craig Ringer
– Craig Ringer, Commented Jul 1, 2014 at 7:49
@CraigRinger, I need to store the select result into table and need to update that table. — Sarfaraz Makandar
– Sarfaraz Makandar, Commented Jul 1, 2014 at 7:56
You can't SELECT INTO a table. Are you looking for INSERT INTO ... SELECT ...? — Craig Ringer
– Craig Ringer, Commented Jul 1, 2014 at 7:59
I want select numb,rank into temp_t from myview; to be done there. — Sarfaraz Makandar
– Sarfaraz Makandar, Commented Jul 1, 2014 at 8:04

Nick Barnes · Accepted Answer · 2014-07-01 12:30:11Z

1

Your function might say RETURNS TABLE(...), but it is not constructing a database table for you. "Table" in this context means something entirely different; a better description of the return type would be "record set" (in fact, it's little more than syntactic sugar for RETURNS SETOF RECORD).

To build a new database table from a query result, use the CREATE TABLE AS statement.

edited Jul 1, 2014 at 12:30

answered Jul 1, 2014 at 11:16

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

2 Comments

Nick Barnes Over a year ago

As a side note, there is a SELECT ... INTO <table> SQL statement which does basically the same thing as CREATE TABLE AS, but it is unrelated to the SELECT ... INTO <variable> plpgsql statement (and can't even be used in plpgsql, due to the conflicting syntax).

Sarfaraz Makandar Over a year ago

Exactly! This is what I wanted. Thank you so much.

Collectives™ on Stack Overflow

PostgreSQL: Executing dynamic command using EXECUTE .. INTO

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related