Here the media_id will be checked with ('24,25') and not with (24,25).
You might change the datatype from varchar to int array in test function and use "any" in the place of "IN" clause like this,
CREATE FUNCTION test(int[]) RETURNS int2 AS '
DECLARE
id_list ALIAS FOR $1;
BEGIN
INSERT INTO history (media_id, media_type) SELECT media.media_id,
media.media_type WHERE media.media_id =ANY(id_list);
.
.
and
SELECT test( array['24,25,26,27']);
On 7/12/05, Adam O'Toole <adamnb1@hotmail.com> wrote: I am trying to INSERT multiple rows to a table using a stored procedure
something like this:
CREATE FUNCTION test(varchar) RETURNS int2 AS '
DECLARE
id_list ALIAS FOR $1;
BEGIN
INSERT INTO history (media_id, media_type) SELECT media.media_id,
media.media_type WHERE media.media_id IN (id_list);
.
.
So I would call this function by passing desired media_id's to be put in the
history table like this:
SELECT test( '24,25,26,27' );
In the INSERT statement, I am taking values from a table called Media, and
adding a row to a table called History. In this example, the only rows
copied would be rows where the media_ID was 24,25,26 or 27. This function
is working for me, but it only works if the varChar being passed has only
one value, like this:
SELECT test('24'); This works.
But when I try to pass more then one value in the list ( '24,25'), the
function runs with no errors but does not add the rows, it does nothing.
What am I doing wrong? Do I have the syntax wrong for using INSERT with a
SELECT-IN statement?
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.