Postgresql function for passing arguments dynamically

Question

I created a function abcd_insert() which inserts data into table abcd, which has 8 columns. The code inside the function looks similar to below :

BEGIN  
 INSERT INTO abcd

 VALUES 
      (
    x                 ,
    y                 ,
    select sum(count) from (select count(*) from a where a1 = x and a2 = y and a3 = 1 union all select count(*) from b where b1 = x and b2 = y and b3 = 1 ) as n1,
    select sum(count) from (select count(*) from a where a1 = x and a2 = y and a2 = 2 union all select count(*) from b where b1 = x and b2 = y and b3 = 2 ) as n2 ,
    select sum(count) from (select count(*) from a where a1 = x and a2 = y and a2 = 3 union all select count(*) from b where b1 = x and b2 = y and b3 = 3 ) as n3 ,
    select sum(count) from (select count(*) from a where a1 = x and a2 = y and a2 = 4 union all select count(*) from b where b1 = x and b2 = y and b3 = 4 ) as n4 ,
    select sum(count) from (select count(*) from a where a1 = x and a2 = y and a2 = 5 union all select count(*) from b where b1 = x and b2 = y and b3 = 5 ) as n5 ,
    SELECT sum(q1) from
    (SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END as q1 FROM p1 where p11 = x and p12 = y union all
    SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END as q1 FROM p2 where p21 = x and p22 = y )  as q1
  ); 
END;

'x' and 'y' are my input parameters whose values will be passed to the function abcd_insert(). a,b,p1 and p2 are tables within the same schema. When I pass 'x' and 'y' to the function at run time, it throws error. Can someone please tell me what I am doing wrong here.

@VaoTsun : Function definition is mentioned above. I wrote error is thrown when values are passed at runtime. Instead, I am unable to create the function itself. It says, "syntax error at or near 'select'" — Random Guy, Feb 12 '18 at 10:00
When asking for help with an error message, it's important to include the *full text* of the error you're getting; it may include details which, while not helpful to you, provide a vital clue for someone helping you. It's also a good idea to include enough code to actually reproduce the error - in this case, you are missing the `CREATE FUNCTION` statement, and the example call to the function, leaving us to guess, and maybe guess wrong. This is known as providing a [mcve]. — IMSoP, Feb 12 '18 at 10:35

Vincent Zhang · Answer 1 · 2018-02-12T08:01:56.417

0

I think you'd better to specify the column names in your insert statement.

Insert into abcd ("column1",...,"column8") values ...

And please post the error, so that others can help.

edited Feb 12 '18 at 08:01

answered Feb 12 '18 at 07:55

Vincent Zhang

369
3
11

If at all, identifiers need to be enclosed in double quotes. Single quotes are for string constants. `'column1'` is not a valid column name in SQL – Feb 12 '18 at 07:58
Pardon me. I wrote error is thrown when values are passed at runtime. Instead, I am unable to create the function itself. It says, "syntax error at or near 'select'". – Random Guy Feb 12 '18 at 09:57

score 0 · Answer 2 · answered Feb 12 '18 at 10:18

with your sample code, you need brackets around queries, so you would use their result, eg:

t=# create table t (i int, e int);
CREATE TABLE
t=# create or replace function f(x int) returns void as $$
begin
 insert into t values (x, (select 1 where x > 0));
end;
$$ language plpgsql;
CREATE FUNCTION
t=# select f(1);
 f
---

(1 row)

t=# select * from t;
 i | e
---+---
 1 | 1
(1 row)

Postgresql function for passing arguments dynamically

2 Answers2