Re: INSERT INTO from a SELECT query

From: "Adam O'Toole" <adamnb1(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: INSERT INTO from a SELECT query
Date: 2005-07-13 16:05:17
Message-ID: BAY102-F25E5A15CD7AA297D185641FBDE0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message John Tulodziecki 2005-07-13 16:05:57 Client-Server Example
Previous Message Karsten Hilbert 2005-07-13 15:56:03 chosing a database name

Browse pgsql-novice by date

  From Date Subject
Next Message Doug Hall 2005-07-13 20:35:35 Help! Not enough swap space?
Previous Message Tino Wildenhain 2005-07-13 13:58:34 Re: INSERT INTO from a SELECT query