Re: [SQL] Scaler forms as function arguments

[Joe Conway]

Richard Huxton wrote:
On Wednesday 26 November 2003 15:40, Andreas Tille wrote:
I want to write a function of the following type

  CREATE FUNCTION test ( <scalar form type> )
        RETURNS setof MyTable
        AS
        'SELECT * FROM MyTable WHERE id IN $1'
        LANGUAGE 'SQL' ;
Not as you've done it. You could pass in text "(1,2,3)", build your query and 
use EXECUTE to execute it. Alternatively, you might be able to do it with an 
array parameter (sorry, I don't use arrays, so I can't be sure).
In 7.4 you could use an array. It would look like this:

CREATE TABLE mytable (id int, idval text);
INSERT INTO mytable VALUES (1,'a');
INSERT INTO mytable VALUES (2,'b');
INSERT INTO mytable VALUES (3,'c');
CREATE FUNCTION test (int[]) RETURNS setof MyTable AS '
SELECT * FROM mytable WHERE id = ANY ($1)
' LANGUAGE 'SQL' ;
regression=# SELECT * FROM test(ARRAY[1,3]);
 id | idval
----+-------
  1 | a
  3 | c
(2 rows)
HTH,

Joe



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]