CODEWITHSUNDEEP
postgresqluser-defined-functionsplpgsqlexecutedynamic-sql
Ali
Ali

Reputation: 3

postgresql functions - execute 'create table...' - unexpected results

I'm writing a function which does the following:

Create a temporary table with a single field. This field is the result of the sum of up to 5 variables from a specific table.

Let's say I have the following table:

create table src (x1 numeric, x2 numeric);
insert into src values (2,1),(5,2),(10,4);

My code is:

create or replace function qwert(cod numeric, v1 numeric default 0
    , v2 numeric default 0, v3 numeric default 0, v4 numeric default 0,
    v5 numeric default 0)
returns numeric as
$func$
declare vv numeric;
begin
vv = v1+v2+v3+v4+v5;
execute '
    drop table if exists t' || cod || '; 
    create temporary table t' || cod || ' as 
    select ' || vv || ' ;'
    ;
return vv;
end
$func$ language plpgsql;

If I run: select qwert(1, x1,x2) from src;

The expected result is a table t1:

 column1 
---------
       3
       7
       14
(3 rows)

Instead the result is:

db1=# select * from t1;
 ?column? 
----------
       14
(1 row)

In my code, line: return vv; is only there to check whether vv was being created correctly.

Could someone help you this?

Upvotes: 0

Views: 6277

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656794

Would work like this:

CREATE OR REPLACE FUNCTION qwert(_tbl text, cols text[])
  RETURNS numeric AS
$func$
BEGIN

EXECUTE format('
     DROP TABLE IF EXISTS %1$I;
     CREATE TEMPORARY TABLE %1$I AS 
     SELECT %2$s AS col_sum FROM src;'
   ,_tbl
   ,(SELECT string_agg(quote_ident(i), ' + ') FROM unnest(cols) i)
    );

RETURN 1;  -- still unclear? Add yourself ...
END
$func$ LANGUAGE PLPGSQL;

Call:

SELECT qwert('t1', ARRAY['x1','x2']);

Or:

SELECT qwert('t1', '{x1,x2}');

format() requires Postgres 9.1 or later.

I use a text parameters for the temp table name and an array of text for the column names, then build the expression with a combination of unnest(), quote_ident() and string_agg(). Don't forget to name the column (col_sum in my ex.).

Details about sanitizing values for use as identifiers in this related answer on dba.SE. You can pass any number of columns this way.

Upvotes: 1

Related Questions