Обсуждение: INSERT INTO from a SELECT query

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?

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']);





--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

Am Dienstag, den 12.07.2005, 12:47 -0300 schrieb Adam O'Toole:
> 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?
>
No, you only do the IN statement wrong. This is not related to
the INSERT. IN wants SQL literal, not a text argument or anything.
All you can do is to either use a function from contrib to
ilterate thru an array instead or build up the SQL from text
string and use EXECUTE and friends - see pl/pgsql reference and
examples.

Regards
Tino

I solved it. The statment worked as is, I just had to use dynamic SQL (put
the statement in a string and the EXECUTE the string). Here is what I did:

CREATE FUNCTION test(varchar)  RETURNS int2 AS'
DECLARE
   id_list ALIAS FOR $1;
   query varchar;
BEGIN
   query := '' INSERT INTO history (media_id, media_type) SELECT
media.media_id, media.media_type WHERE media.media_id IN ( '' || id_list ||
'')'';

   EXECUTE query;

Now I can call this function like this
  SELECT test( '24,25,26,27,28,29' );
and it will execute the INSERT statement for each value in the passed
varchar


-Thanks guys for your input

Adam