0

I am trying to execute dynamic query using PostgreSQL. I have a function with three parameters and need to suffix that parameters with some variables(to make view name) and need to retrieve rows from that variables(view) and return the result.

Example 

create or replace function testing(abc varchar,def varchar,ghi varchar)
returns setof record as
$BODY$
Declare
      temptable1 varchar :='temp1_';
      temptable2 varchar :='temp2_';
      viewname varchar :='view_';
Body
      temptable1 := temptable1||abc;
      temptable2 := temptable2||def;
      viewname := viewname||ghi;

      execute 'Drop table if exists'||temptable1;
      execute 'Drop table if exists'||temptable2;

      WITH cm
      AS
      (
      SELECT "ssno","rlno",
           DENSE_RANK() OVER(Partition by "ssno" Order By "rlno"     )FoundIn
      From viewname;
      )
    SELECT DISTINCT * INTO temptable1
    FROM cm
    WHERE FoundIn > 1;

    SELECT DISTINCT cr."ssno", cdr."rlno"
        INTO temptable2
        FROM temptable1 l1
        INNER JOIN viewname cr on l1."rlno" = cr."rlno"
        ORDER BY "rlno";

    /* Need to result should be display for below query */
    SELECT DISTINCT  cr.ssno AS Nos, cr.rlno, FoundIn,cr.Name, cr.Address,
from temptable1 l1
inner join viewname cr on l1.rlno = cr.rlno
order by "rlno"

end;
$BODY$
Language plpgsql;
Sarfaraz Makandar
  • 5,933
  • 16
  • 59
  • 84
  • Are you just asking how to return the query? Prefix it with `RETURN QUERY`. – Nick Barnes Jun 14 '14 at 06:48
  • @Nick Barmes, I am just confusing about how to execute WITH and SELECT statements and return the result of last SELECT statement. Shall I use EXECUTE or just RETURN QUERY before the SELECT statement. – Sarfaraz Makandar Jun 14 '14 at 06:51
  • Just `RETURN QUERY` ([example](http://sqlfiddle.com/#!15/4e0fe/2)). You only need `EXECUTE` when your query is stored in a string. – Nick Barnes Jun 14 '14 at 07:10
  • @Nick Barnes, I am getting notice that table does not exists. Not proceeding for next step from drop table. – Sarfaraz Makandar Jun 14 '14 at 07:25
  • 1
    Sorry, I didn't read your code closely enough. Table names cannot be variables in ordinary queries; you need to build all your queries as strings and `EXECUTE` them. Returning the result is simple, e.g. `RETURN QUERY EXECUTE 'SELECT * FROM ' || tablename;` – Nick Barnes Jun 14 '14 at 08:12
  • 1
    possible duplicate of [Postgres Dynamic Query Function](http://stackoverflow.com/questions/10639963/postgres-dynamic-query-function) – Daniel Vérité Jun 14 '14 at 17:23
  • @DanielVérité, The above query taking too much time for execution for 54k records. I have added index also. – Sarfaraz Makandar Jun 18 '14 at 07:23
  • @NickBarnes, The above query taking too much time for execution for 54k records. I have added index also. – Sarfaraz Makandar Jun 18 '14 at 07:25

0 Answers0