PostgreSQL function parameters not being passed correctly

Question

I have a function that takes in an argument which is meant to be passed to a RETURN QUERY and the data is fed in from an underlying view. Now I get zero rows when I execute the function but when I run the select statement on it's own I get results.
So part of me thinks that the function parameter is not being read at all. Any help/suggestions is appreciated.

create or replace function my_trans(IN inid character)
RETURNS TABLE(
  a_date timestamp without time zone, 
  pnt_sys character, 
  nval numeric, 
  pnt_type character, 
  gpid integer)  
as $$
#variable_conflict use_column
BEGIN
    RETURN QUERY
    SELECT 
             BORN_DATE,
             MY_SYSTEM,
             MY_VALUE,
             MY_TYPE,
             MY_PAY_ID
     FROM vw_psr where nid ~* '$1';
end;
$$ language plpgsql

(IN inid character varying) or (IN inid text) , supposing you want a string argument. — joop
– joop, Commented Sep 27, 2016 at 9:39
Also, do not quote the placement of your parameter ($1) -- it will mean literally the string '$1' (you can use the identifier inid too because you named your parameter). Also note that in its current state a LANGUAGE sql function might be better suited for your needs. — pozs
– pozs, Commented Sep 27, 2016 at 9:46
@joop.. cheers.. tried that, no luck, although I think that's hardly the issue. — ribbit
– ribbit, Commented Sep 27, 2016 at 10:05
The wierd thing is if I re-crate the function without parameters and no filter on the where clause then the function essentially works but not what I want. — ribbit
– ribbit, Commented Sep 27, 2016 at 10:13
@pozs .. That's has worked for me .. Smashing stuff, thanks a bunch!! — ribbit
– ribbit, Commented Sep 27, 2016 at 10:21

user330315 · Accepted Answer · 2016-09-27 09:57:02Z

3

There are two things wrong with your function:

don't use character - use text or varchar instead
don't put parameter names in single quotes

Unrelated to your problem, but a simple "SELECT function" is better written as a plain SQL function:

create or replace function my_trans(p_inid text)
RETURNS TABLE(
  a_date timestamp without time zone, 
  pnt_sys character, 
  nval numeric, 
  pnt_type character, 
  gpid integer)  
as $$
    SELECT BORN_DATE,
           MY_SYSTEM,
           MY_VALUE,
           MY_TYPE,
           MY_PAY_ID
    FROM vw_psr 
    where nid ~* p_inid;
$$ language sql;

It's also a good coding convention to use some kind of prefix for parameters and local variables to avoid a name clash between them and column names.

answered Sep 27, 2016 at 9:57

user330315

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

1 Comment

ribbit Over a year ago

spot on .. thanks for your suggestions. It's been very helpful indeed.

Collectives™ on Stack Overflow

PostgreSQL function parameters not being passed correctly

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related