How to reference arguments between nested and top-level function

Question

Using sql on postgres 9.3 (MacOSX), how would I reference the arguments from a nested function to the arguments of the top-level function? Below is a dummy example.

CREATE FUNCTION f1(x TEXT, y TEXT) RETURNS SETOF some_tbl AS $$
   SELECT col1, col2, col3
   FROM some_other_tbl
   WHERE col1=x AND col2=y
   GROUP BY col1;
$$ LANGUAGE 'sql';


CREATE FUNCTION f2(x TEXT, y TEXT) RETURNS void AS $$
   COPY (SELECT * FROM f1(x, y) TO 'myfilepath/test.csv'
$$ LANGUAGE 'sql';

I have looked through the Arguments for SQL Functions and found that you can reference arguments using the syntax $n. So I substituted (x, y) in the nested function with ($1, $2) but when calling f2 it gives the error messages ERROR: there is no parameter $1 SQL state: 42P02 Context: SQL function "f2" statement 1

Vladimir Kroz · Accepted Answer · 2013-11-13 01:15:56Z

1

($1, $2) notation will work when you declare your function with unnamed arguments, like in this example:

CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
   SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

answer
--------
  3

In your case you explicitly define names of the function arguments, so you should use names according to definition

answered Nov 13, 2013 at 1:15

Vladimir Kroz

5,3876 gold badges43 silver badges50 bronze badges

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

2 Comments

jO. Over a year ago

Thanks for your comment. So, I removed x, y (i.e. only TEXT, TEXT) and adjusted f1 to ...WHERE col1=$1 AND col2=$2 and then in the nested function again tried to reference by using $1, $2 but with the same error message. When I kept the define arguments (x, y) and referenced by ($x, $y) that did not work either.

jO. Over a year ago

Thanks again. In my above example code, the problem is explicitly with the COPY function. See solution

Collectives™ on Stack Overflow

How to reference arguments between nested and top-level function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related