Skip site navigation (1) Skip section navigation (2)

Re: INSERT INTO from a SELECT query

From: Tino Wildenhain <tino(at)wildenhain(dot)de>
To: Adam O'Toole <adamnb1(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: INSERT INTO from a SELECT query
Date: 2005-07-13 13:58:34
Message-ID: 1121263114.20011.18.camel@sabrina.peacock.de (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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



In response to

Responses

pgsql-novice by date

Next:From: Adam O'TooleDate: 2005-07-13 16:05:17
Subject: Re: INSERT INTO from a SELECT query
Previous:From: Gnanavel SDate: 2005-07-13 13:42:54
Subject: Re: INSERT INTO from a SELECT query

pgsql-general by date

Next:From: Google MikeDate: 2005-07-13 14:00:22
Subject: What's Popular for CMS and RAD with PHP/PostgreSQL?
Previous:From: Roman NeuhauserDate: 2005-07-13 13:58:09
Subject: Re: 7.4.7: strange planner decision

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group