Re: INSERT INTO from a SELECT query

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(at)hotmail(dot)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.