0

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

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
jO.
  • 3,384
  • 7
  • 28
  • 38

1 Answers1

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

Vladimir Kroz
  • 5,237
  • 6
  • 39
  • 50
  • 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. Nov 13 '13 at 01:37
  • Thanks again. In my above example code, the problem is explicitly with the COPY function. [See solution](http://stackoverflow.com/questions/19918385/calling-a-stored-procedure-within-a-stored-procedure) – jO. Nov 14 '13 at 21:18