Re: Scaler forms as function arguments

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