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

Re: INSERT INTO from a SELECT query

From: Gnanavel S <s(dot)gnanavel(at)gmail(dot)com>
To: Adam O'Toole <adamnb1(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: INSERT INTO from a SELECT query
Date: 2005-07-13 13:42:54
Message-ID: eec3b03c050713064266e2d553@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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.

In response to

pgsql-novice by date

Next:From: Tino WildenhainDate: 2005-07-13 13:58:34
Subject: Re: INSERT INTO from a SELECT query
Previous:From: Martin AtukundaDate: 2005-07-13 12:05:29
Subject: PQresultStatus for a transaction

pgsql-general by date

Next:From: Ron MayerDate: 2005-07-13 13:50:23
Subject: Re: Converting MySQL tinyint to PostgreSQL
Previous:From: Tom LaneDate: 2005-07-13 13:35:55
Subject: Re: getting the ranks out of items with SHARED

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