CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sqlquoting
octobot
octobot

Reputation: 89

Error when calling a plpgsql function

Following is my function created in PostgreSQL 9.2

CREATE OR REPLACE function fn_exp_settl_cr
(branchid int
 ,vrid int
 ,ddate date
 ,acid int
) 
returns 
TABLE ( trno character varying(15), trdate timestamp without time zone, netamt money, duedate 
        timestamp without time zone,tranid integer, acyrid integer, custrefno  
        charactervarying(26), orderdate timestamp without time zone) as $$
declare 
sql text;
begin
execute 'select string_agg(s.qry,'' UNION ALL '')  from (select ''SELECT 
         TrNo,TrDate,NetAmt,DueDate,TranId,AcYrId,custrefno,orderdate FROM '||t.relname||' WHERE 
         AdjInBill = false And  ExpSettled = false And (BranchID = 1) AND (VrId = 29) and   
         DueDate <= ''2014-07-24'' and AcId=76 '' qry from (select relname from 
         pg_stat_user_tables where relname ilike ''gtab09%'') t )s ' into sql;
execute sql;
end;
$$ language plpgsql

It creates successfully but when calling this function, i.e.:

select * from fn_exp_settl_cr(1,2,'2014-07-24',4)

I get the following error:

ERROR: missing FROM-clause entry for table "t" LINE 1: ...DueDate,TranId,AcYrId,custrefno,orderdate FROM '||t.relname|...

Upvotes: 0

Views: 107

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656411

CREATE OR REPLACE function fn_exp_settl_cr(_branchid int, _vrid int
                                         , _ddate date, _acid int)
  RETURNS TABLE (
   trno character varying(15)
 , trdate timestamp
 , netamt money
 , duedate timestamp
 , tranid integer
 , acyrid integer
 , custrefno  character varying(26)
 , orderdate timestamp
) AS
$func$
BEGIN

RETURN QUERY EXECUTE (
   SELECT string_agg
            (format
               ($$SELECT TrNo,TrDate,NetAmt,DueDate,TranId,AcYrId
                        ,custrefno,orderdate
                  FROM   %I
                  WHERE  AdjInBill = false
                  AND    ExpSettled = false
                  AND    BranchID = $1
                  AND    VrId = $2
                  AND    DueDate <= $3
                  AND    AcId = $4
                $$
               ,t.relname
               )
            ,' UNION ALL '
            )
   FROM   pg_class t
   WHERE  t.relkind = 'r' -- ordinary table
   AND    t.relname ILIKE 'gtab09%'
   )
USING _branchid, _vrid, _ddate, _acid;

END
$func$ LANGUAGE plpgsql;

Major points

  • Your format is hardly readable, which is probably the main reason you can't find the problem.

  • Typo in charactervarying(26). Fix: character varying(26). I would just use varchar or text, though.

  • You got confused with multiple layers of quoting. The fix is to use dollar-quoting and format() to build your string. Also, one or two layers can just be simplified away.

  • You are not actually using your input parameters. Probably because you got stuck and are testing with literal values. While being at it, I built those in, too, with a USING clause.

  • I suggest not to use the same identifiers as parameter names. Those are visible in the function body everywhere and easily lead to naming conflicts. Prepended with '_'.

  • You didn't actually return anything. Use RETURN QUERY EXECUTE instead of just EXECUTE for that end.

And why retrieve table names from the statistics view pg_stat_user_tables? It's cheaper and more reliable to get it from the information schema or the catalog table pg_class. Changed that, too.

Upvotes: 2

Related Questions