[SQL] Array from INSERT .. RETURNING in plpgsql?

2008-10-07 Thread Erik Jones 

Ok, so the following works:

pagila=# select array(select s.i from generate_series(1, 10) s(i));
  ?column?
  
{1,2,3,4,5,6,7,8,9,10}
   (1 row)

but this doesn't:

pagila=# create or replace function testfun() returns void as $$
declare
vals int[];
query text;
begin
query := 'insert into test select s.i from generate_series(1,10)  
s(i) returning i;';

execute query into vals;
raise notice 'vals dim: %', array_upper(vals, 1);
raise notice 'vals[3]: %', vals[3];
end;
$$ language plpgsql;
CREATE FUNCTION
Time: 3.319 ms
pagila=# select testfun();
ERROR:  array value must start with "{" or dimension information
CONTEXT:  PL/pgSQL function "testfun" line 6 at execute statement

Is there any way to do what I'm trying without explicity looping over  
the results of the insert?


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
(415) 963-4410 x 260
Location: US/Pacific
IRC: mage2k





--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Array from INSERT .. RETURNING in plpgsql?

2008-10-07 Thread Pavel Stehule 
Hello

I afraid, it isn't possible. You cannot use returning in subqueries,
and returned value from RETURNING clause isn't array.

you can do

declare
   _sa int[] = '{}';
   _a int;
begin
  for a in execute 'insert  returning i' loop
_sa := _sa || _a;
  end loop;
  return _sa;
end;

but this query will be slow for bigger returned arrays than 1 fields
regards
Pavel Stehule

  query := 'insert into test select s.i from generate_series(1,10)
s(i) returning i;';

2008/10/7 Erik Jones <[EMAIL PROTECTED]>:
> Ok, so the following works:
>
> pagila=# select array(select s.i from generate_series(1, 10) s(i));
>  ?column?
>  
> {1,2,3,4,5,6,7,8,9,10}
>   (1 row)
>
> but this doesn't:
>
> pagila=# create or replace function testfun() returns void as $$
> declare
>vals int[];
>query text;
> begin
>query := 'insert into test select s.i from generate_series(1,10) s(i)
> returning i;';
>execute query into vals;
>raise notice 'vals dim: %', array_upper(vals, 1);
>raise notice 'vals[3]: %', vals[3];
> end;
> $$ language plpgsql;
> CREATE FUNCTION
> Time: 3.319 ms
> pagila=# select testfun();
> ERROR:  array value must start with "{" or dimension information
> CONTEXT:  PL/pgSQL function "testfun" line 6 at execute statement
>
> Is there any way to do what I'm trying without explicity looping over the
> results of the insert?
>
> Erik Jones, Database Administrator
> Engine Yard
> Support, Scalability, Reliability
> (415) 963-4410 x 260
> Location: US/Pacific
> IRC: mage2k
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] many-to-many relationship

2008-10-07 Thread Steve Midgley 

At 06:20 AM 10/7/2008, [EMAIL PROTECTED] wrote:

Date: Mon, 6 Oct 2008 15:08:02 +0200
From: Louis-David Mitterrand <[EMAIL PROTECTED]>
To: [email protected]
Subject: many-to-many relationship
Message-ID: <[EMAIL PROTECTED]>
X-Archive-Number: 200810/13
X-Sequence-Number: 31655

Hi,

Say you have several objects (tables): person, location, event, etc. 
all

of which can have several images attached.

What is the best way to manage relations between a single 'image' 
table

and these different objects?

For now each 'image' row has pointers to id_person, id_location,
id_event, etc. (only one of which is used for any given row).

Is there a better way, more elegant way to do it, without using
redundant id_* pointers on each row and yet still enforce foreign 
keys?


Thanks,


Hi,

I think the relationship tables method works pretty well but I have 
another suggestion. You could store the Foreign table name within image 
table as well as the Foreign key.


|id|image_url|f_table|f_key
|1 |url..|person |1234
|2 |url2.|event  |5678

I think this is called a "polymorphic join" but I could be wrong about 
that. I'd guess you could construct a rule or trigger to validate the 
foreign key data on insert/update but that's out of my skill area.


Hope that helps a little,

Steve


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql