current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
    
Sorry. I edited the question. Hope it's clear now. –  Ali Oct 21 '13 at 17:06
    
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

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
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
    
@Ali: Consider the updated answer. –  Erwin Brandstetter Oct 21 '13 at 18:13
    
That works fine! Thanks Erwin. –  Ali Oct 21 '13 at 18:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.