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

Question

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?

What do you need the temp table for? Or is that just a (failed) attempt to get a set of rows from the function? Also, your question still doesn't add up. Do you want to sum `x1 + x2` from table `src`? — Erwin Brandstetter, Oct 21 '13 at 17:16
I need a temp table to store new variables which I need to use in further calculations. In each session my input variables may change. I want to sum up to five columns, therefore I set default values to zero. As an example, I'm just trying to sum two columns (x1 and x2 from src). the resulting table should have x1+x2 (3,7,14). Big sorry. My query should be: select qwert(1, x1,x2) from src; (hence the confusion, I think...) — Ali, Oct 21 '13 at 17:32

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

1

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.

edited Apr 13 '17 at 12:42

Community

1
1

answered Oct 21 '13 at 16:40

Erwin Brandstetter

605,456
145
1,078
1,228

I run your code and the result is a table whose values are 10,4,0,0,0. I think my question wasn't clear. I edited just now. Thanks. – Ali Oct 21 '13 at 17:13
My table must be the result of a select query. The real table I'm working with has thousands of records. – Ali Oct 21 '13 at 18:10
Thanks. I'd appreciate if you could also consider https://stackoverflow.com/questions/51004980/what-is-the-order-between-parsing-and-execution-of-pl-pgsql-functions – Jun 25 '18 at 04:05

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

1 Answers1