How to reference named parameters in Postgres sql functions?

Question

Postgres noobie here.

I am trying to convert a SQL Server stored process to a Postgres function. It is currently not possible to figure out how to include this SQL string in 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 get the error "column n does not exist". How to refer to parameters in select statements in Postgres functions?

UPDATE:

Postgres Function Definition

 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; 

Answer 1

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

Therefore, you must access the args function using $ X, where X is the ordinal position of the function argument list (starting at 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; 

Answer 2

You cannot use named parameters in a function defined using language = SQL.

You need to use the $ 1 placeholder.

 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 is described in the manual: http://www.postgresql.org/docs/9.0/static/xfunc-sql.html

As for the SQL function itself, these names are just a decoration; you should still reference parameters like $ 1, $ 2, etc. inside body functions

Edit

Since version 9.2, you can use named parameters with a (simple) SQL function
http://www.postgresql.org/docs/9.2/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS