| From: | "Adam O'Toole" <adamnb1(at)hotmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Cc: | pgsql-novice(at)postgresql(dot)org | 
| Subject: | INSERT INTO from a SELECT query | 
| Date: | 2005-07-12 15:47:57 | 
| Message-ID: | BAY102-F24BE16A513D4B2037E845CFBDF0@phx.gbl | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general pgsql-novice | 
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?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Karl O. Pinc | 2005-07-12 15:49:04 | Re: Db and schema names in logged errors | 
| Previous Message | Roman Neuhauser | 2005-07-12 15:35:35 | Re: Update more than one table | 
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Aris Aridis | 2005-07-12 16:27:17 | |
| Previous Message | Tom Lane | 2005-07-12 15:28:58 | Re: ERROR: plperl functions cannot take type anyarray |