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

Flimzy
  • 68,325
  • 15
  • 126
  • 165
jO.
  • 3,084
  • 7
  • 26
  • 36

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,048
  • 5
  • 37
  • 49
  • 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