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
– Erwin Brandstetter, Commented Oct 21, 2013 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
– Ali, Commented Oct 21, 2013 at 17:32

Community · Accepted Answer · 2017-04-13 12:42:39Z

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, 2017 at 12:42

CommunityBot

11 silver badge

answered Oct 21, 2013 at 16:40

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ali Over a year ago

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 Over a year ago

My table must be the result of a select query. The real table I'm working with has thousands of records.

user3284469 Over a year ago

Thanks. I'd appreciate if you could also consider stackoverflow.com/questions/51004980/…

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related