how to pass named parameters to where clause in stored procedure in postgres

Question

I tried in Postgres 9.3

CREATE OR REPLACE FUNCTION f_tsd(koakuupav date, kolkuupaev date)
  RETURNS TABLE (
isikukood int
) AS
$func$
select 1 where current_date between koakuupaev and kolkuupaev
$func$ LANGUAGE sql STABLE;

but got error

ERROR:  column "koakuupaev" does not exist

In plpgsql it causes same error at run time:

CREATE OR REPLACE FUNCTION f_tsd(koakuupav date, kolkuupaev date)
  RETURNS TABLE (
isikukood int
) AS
$func$
BEGIN
RETURN QUERY  select 1 where current_date between  koakuupaev and kolkuupaev;
end; $func$ LANGUAGE plpgsql stable;

select * from f_tsd(current_date, current_date)

How to pass parameters to where clause in function in Postgres ? According to docs named parameters must work.

Which Postgres version are you using? The ability to use a named parameter inside a SQL function was added in 9.2 if I'm not mistaken. If you are on an older version, you need to use $1 and $2. But in either case you need to change the data type of the returned table to integer or cast the 1 to a numeric value (I guess that's the problem with your second version) — user330315
– user330315, Commented Nov 1, 2014 at 14:39
I'm using 9.3. According to docs named parameters must work. I changed return type to integer but problem persists — Andrus
– Andrus, Commented Nov 1, 2014 at 18:03

Clodoaldo Neto · Accepted Answer · 2014-11-01 19:44:12Z

3

The first parameter name koakuupav is missing an e in pav in instead of paev

f_tsd(koakuupav date, kolkuupaev date)

between  koakuupaev and kolkuupaev

answered Nov 1, 2014 at 19:44

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

how to pass named parameters to where clause in stored procedure in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related