How do I reference named parameters in Postgres sql functions?

Question

Postgres noobie here.

I'm trying to convert a SQL Server stored proc into a Postgres function. Currently cannot figure out how to turn this SQL line into Postgres.

SQL Server:

input: @name = null

SELECT *
FROM table
WHERE name = ISNULL(@name, name)

Postgres:

input: n = null

SELECT *
FROM table
WHERE name = COALESCE(n, name)

I'm getting the error "column n does not exist." How do I reference parameters in select statements in Postgres functions?

UPDATE:

Definition of Postgres function

CREATE OR REPLACE FUNCTION fn_name (n VARCHAR(32) = NULL, name OUT varchar(32), description OUT varchar(64))
RETURNS setof record
AS 
$$
    SELECT u.name
        , u.description
    FROM table_a u
    WHERE u.name = COALESCE(n, u.name);

$$
LANGUAGE sql;

dbenhur · Accepted Answer · 2018-04-16 20:26:18Z

22

REVISED: As pointed out in comments, this answer was accurate when written in early 2012, but named parameters have been supported since v9.2, released late 2012.

Parameter names are merely decoration when your function is in language SQL. You can use the parameters by name in stored procedures defined as language plpgsql.

Consequently, you must refer to the function args using $X where X is the ordinal position of the function's argument list (starting with 1).

CREATE OR REPLACE FUNCTION fn_name (
  n VARCHAR(32) = NULL,
  OUT name varchar(32),
  OUT description varchar(64) )
RETURNS setof record
AS 
$$
    SELECT u.name
        , u.description
    FROM table_a u
    WHERE u.name = COALESCE($1, u.name);
$$
LANGUAGE sql;

edited Apr 16, 2018 at 20:26

answered Mar 19, 2012 at 14:26

dbenhur

20.5k4 gold badges51 silver badges47 bronze badges

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

1 Comment

Josh Diehl Over a year ago

per @a_horse_with_no_name 's point on another answer, named support has been present since 9.2 postgresql.org/docs/9.2/static/…

score 13 · Accepted Answer · 2013-05-13 13:31:51Z

You cannot use named parameters in a function that is defined with language=SQL.

You need to use the the placeholder $1.

CREATE OR REPLACE FUNCTION fn_name (n VARCHAR(32) = NULL, name OUT varchar(32), description OUT varchar(64))
RETURNS setof record
AS 
$$
    SELECT u.name
        , u.description
    FROM table_a u
    WHERE u.name = COALESCE($1, u.name);

$$
LANGUAGE sql;

This behaviour is documented in the manual: http://www.postgresql.org/docs/9.0/static/xfunc-sql.html

So far as the SQL function itself is concerned, these names are just decoration; you must still refer to the parameters as $1, $2, etc within the function body

Edit

Since version 9.2 it is possible to use named parameters with (plain) SQL function
http://www.postgresql.org/docs/9.2/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS

Collectives™ on Stack Overflow

How do I reference named parameters in Postgres sql functions?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related