PostgreSQL: get input parameters as row values

Question

I have created a simple function:

create function my fucnction(uuid, uuid, date) returns boolean as
$$
    select ... from t where t.f1 = $1 and t.f2 = $2 and t.f3 = $3;
$$
language sql stable;

It would be great syntax-wise if I could access the input parameters as a single row (which would be equal to ($1, $2, $3) in my function), so I could write:

create function my fucnction(uuid, uuid, date) returns boolean as
$$
    select ... from t where (t.f1, t.f2, t.f3) = <the input parameters row>;
$$
language sql stable;

which would be equal to:

create function my fucnction(uuid, uuid, date) returns boolean as
$$
    select ... from t where (t.f1, t.f2, t.f3) = ($1, $2, $3);
$$
language sql stable;

Is this possible?

...where (t.f1, t.f2, t.f3) = ($1, $2, $3); This should work. Did you tested and it not works? — Oto Shavadze
– Oto Shavadze, Commented Jul 7, 2017 at 9:42
That works great. But I am looking for a syntax replacement for ($1, $2, $3). Something like NEW and OLD which can be used in triggers. Does that exist? — Caroline Kwerts
– Caroline Kwerts, Commented Jul 7, 2017 at 9:50

cole · Accepted Answer · 2017-07-07 10:26:58Z

2

This works for me. I am a big fan of the row() function, especially in count(distinct row()) queries.

create OR REPLACE function my_function(text,text,text) returns bigint as
$$
    select count(*) from t where row(t1,t2,t3) = row($1,$2,$3);
$$
language sql stable;

select * from my_function('a','b','c');

NOTE: this seems to work with your syntax of (t1,t2,t3), as well.

EDIT: I may have misunderstood your question. If you particularly want a named reference like in triggers, you can always pass in a record type (here I am using the type defined by table t). It might be worth looking into the record datatype as well as PLPGSQL if you need a more complex function:

create OR REPLACE function my_function(blast t) returns bigint as
$$

    select count(*) from t where (t1,t2,t3) = blast;
$$
language sql stable;

select * from my_function(row('a','b','c'));

One last alternative - does not give the niceness of the syntax above, presumably because the type is not defined, but if you need separate parameters on input:

create OR REPLACE function my_function(_1 text, _2 text, _3 text) returns bigint as
$$

    select count(*) from t where (t1,t2,t3) = (my_function._1, my_function._2, my_function._3);
$$
language sql stable;

select * from my_function('a','b','c')

edited Jul 7, 2017 at 10:26

answered Jul 7, 2017 at 10:06

cole

1,7572 gold badges17 silver badges23 bronze badges

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

3 Comments

Caroline Kwerts Over a year ago

Regarding your edit, I guess select count(*) from t where (t1,t2,t3) = blast will work then?

cole Over a year ago

Correct - just confirmed. One more alternative I will add, but does not give you the nice where (t1,t2,t3) = blast.

Caroline Kwerts Over a year ago

Thanks! I did now know row(), seems very handy. I will accept after your edit!

Collectives™ on Stack Overflow

PostgreSQL: get input parameters as row values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related