How to assign SELECT statement to variable and execute that variable using PostgreSQL

Question

I am trying to assign the "SELECT" query to variable and then want to execute that variable and display result but got stuck while quoting single quotes.

Example:

 create or replace function test() returns setof record as
 $BODY$
 Declare
      query1 varchar;
 Begin

      query1 := SELECT DISTINCT c."Name", c."Address"
            INTO  temptable
        FROM table1 l
        INNER JOIN tablename c on l."Name" = c."Name"
        ORDER BY "Name";

  /* Here need to execute query1 and get result of it */
 end;
 $BODY$
 language plpgsql;

Question: How to give proper single quote for select statement and execute it?

user330315 · Accepted Answer · 2014-06-13 13:35:34Z

I assume you don't really want a select .. into ... ... as that is the non-standard (obsolete, historic) syntax for create table as select.

As you declared your function as returns setof record I assume you actually want to return a result set. It would be better to use returns table (...) instead because then the usage of the function is easier.

Finally I don't see the reason for dynamic SQL here, but I assume you simply didn't tell us the whole story.

The following should work (untested):

create or replace function test() 
   returns table (name text, address text)
as
 $BODY$
 Declare
      query1 varchar;
 Begin
    query1 := 'SELECT DISTINCT c."Name", c."Address"
        FROM table1 l
        INNER JOIN tablename c on l."Name" = c."Name"
        ORDER BY "Name"';

    return query execute query1;
 end;
 $BODY$
 language plpgsql;

This assumes that Name and Address are both varchar or text columns. If they are not you need to adjust the returns table (text, text) part.

But given your example there is no need for dynamic SQL or even PL/pgSQL, a simple SQL function will do (and will usually be much faster):

create or replace function test() 
   returns table (name text, address text)
as
$body$
 SELECT DISTINCT c."Name", c."Address"
 FROM table1 l
   INNER JOIN tablename c on l."Name" = c."Name"
 ORDER BY "Name"';
$body$
language sql;

^{You should also avoid quoted identifiers. They are more trouble than they are worth in the long run. Just create your tables without quoting the column names.}

Collectives™ on Stack Overflow

How to assign SELECT statement to variable and execute that variable using PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related