pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql - Insert from a record variable?
8+ messages / 4 participants
[nested] [flat]
* plpgsql - Insert from a record variable?
@ 2004-06-11 14:42 Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-19 06:03 ` Re: plpgsql - Insert from a record variable? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Phil Endecott @ 2004-06-11 14:42 UTC (permalink / raw)
To: pgsql-sql
Dear Postgresql experts,
I'm writing a simple plpgsql function that selects a row from a table, modifies it slightly, and inserts the modified version. This sounds simple, but I can't find how to do the insert in a simple generic fashion: although SELECT has a form that puts the result in a record variable, INSERT doesn't seem to have anything similar.
What I'd like to be able to write is something like this:
DECLARE
R RECORD;
BEGIN
SELECT * INTO R FROM TABLE WHERE id=n;
R.something := x;
INSERT INTO TABLE R;
END
But as far as I can see the only syntax that is allowed for INSERT is the normal INSERT INTO TABLE (x,y,z) VALUES (a,b,c). To achieve what I want to do I'd need to iterate over the fields of the record (how?) and build up the string of the INSERT query.
It seems odd that SELECT can use a record variable but INSERT can't, so I wonder if I have missed something. Any suggestions?
Thanks in advance for any help anyone can offer.
Regards,
--Phil.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpgsql - Insert from a record variable?
2004-06-11 14:42 plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
@ 2004-06-19 06:03 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 18:39 ` Re: plpgsql - Insert from a record variable? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Riccardo G. Facchini @ 2004-06-19 06:03 UTC (permalink / raw)
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>; pgsql-sql
--- Phil Endecott <__> wrote:
> Dear Postgresql experts,
>
> I'm writing a simple plpgsql function that selects a row from a
> table, modifies it slightly, and inserts the modified version. This
> sounds simple, but I can't find how to do the insert in a simple
> generic fashion: although SELECT has a form that puts the result in a
> record variable, INSERT doesn't seem to have anything similar.
>
> What I'd like to be able to write is something like this:
>
> DECLARE
> R RECORD;
> BEGIN
> SELECT * INTO R FROM TABLE WHERE id=n;
> R.something := x;
> INSERT INTO TABLE R;
> END
I'm not sure if it will work, but have you tried either two of these
forms?
--
declare r record;
begin
select * into r from table where id = n;
r.something := x;
insert into table select r;
end;
--
declare r record;
begin
select * into r from table where id = n;
r.something := x;
insert into table select r.*;
end;
>
> But as far as I can see the only syntax that is allowed for INSERT is
> the normal INSERT INTO TABLE (x,y,z) VALUES (a,b,c). To achieve what
> I want to do I'd need to iterate over the fields of the record (how?)
> and build up the string of the INSERT query.
>
> It seems odd that SELECT can use a record variable but INSERT can't,
> so I wonder if I have missed something. Any suggestions?
>
> Thanks in advance for any help anyone can offer.
>
> Regards,
>
> --Phil.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
regards,
=====
Riccardo G. Facchini
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpgsql - Insert from a record variable?
2004-06-11 14:42 plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-19 06:03 ` Re: plpgsql - Insert from a record variable? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
@ 2004-06-19 18:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-21 17:23 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2004-06-19 18:39 UTC (permalink / raw)
To: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; +Cc: Phil Endecott <spam_from_postgresql_sql@chezphil.org>; pgsql-sql
"Riccardo G. Facchini" <abief_ag_-postgresql@yahoo.com> writes:
> --- Phil Endecott <__> wrote:
>> I'm writing a simple plpgsql function that selects a row from a
>> table, modifies it slightly, and inserts the modified version. This
>> sounds simple, but I can't find how to do the insert in a simple
>> generic fashion: although SELECT has a form that puts the result in a
>> record variable, INSERT doesn't seem to have anything similar.
> I'm not sure if it will work, but have you tried [snip]
> declare r record;
> insert into table select r.*;
I don't know of any way to handle this in existing releases ---
plpgsql's support for rowtype variables is just too limited.
However, in 7.5 you will be able to do either of
insert into table select r.*;
insert into table values(r.*);
so long as r is declared to be of a named rowtype (not just RECORD;
that doesn't have a known field list to plan the query with). For
instance I was just experimenting with this test case:
create or replace function fooey(int) returns void as '
declare r foo;
begin
select * into r from foo where id = $1;
r.f1 = ''baz'';
insert into foo select r.*;
return;
end' language plpgsql;
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpgsql - Insert from a record variable?
2004-06-11 14:42 plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-19 06:03 ` Re: plpgsql - Insert from a record variable? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 18:39 ` Re: plpgsql - Insert from a record variable? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-06-21 17:23 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-21 18:51 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-22 01:30 ` Re: plpgsql - Insert from a record variable? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Phil Endecott @ 2004-06-21 17:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; Phil Endecott <spam_from_postgresql_sql@chezphil.org>; pgsql-sql
Phil> Insert from a record variable?
Riccardo> Try insert into table select r.*;
Tom> in 7.5
Tom> insert into table select r.*;
Tom> insert into table values(r.*);
Tom> so long as r is declared to be of a named rowtype (not just
Tom> RECORD)
Thanks! Unfortunately I need record, rather than %rowtype. See my later email where I describe how I am trying to use this with inheritance; the function looks up a row in a base table, finds the derived table in which it really exists using pg_class.relname, and then inserts a modified copy of the row in the derived table.
I'm not concerned about the performance issues to do with pre-planning the queries. I think what I really need is an introspection mechanism so that I can loop over each element of the record and construct the insert as a string. Maybe this is possible using a different server-side language? I've just had an idea: perhaps rather than inspecting the record variable to see what fields it contains, I can look at the table to see what columns it contains (since this amounts to the same thing). Presumably I can do this using information_schema.columns. I'll have a go.
--Phil.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpgsql - Insert from a record variable?
2004-06-11 14:42 plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-19 06:03 ` Re: plpgsql - Insert from a record variable? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 18:39 ` Re: plpgsql - Insert from a record variable? Tom Lane <tgl@sss.pgh.pa.us>
2004-06-21 17:23 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
@ 2004-06-21 18:51 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-22 00:57 ` Re: plpgsql - Insert from a record variable? basic <basic@mozdev.org>
1 sibling, 1 reply; 8+ messages in thread
From: Phil Endecott @ 2004-06-21 18:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; pgsql-sql
I wrote:
> perhaps rather than inspecting the record variable to see what fields
> it contains, I can look at the table to see what columns it contains
This is my effort. It doesn't work, because I can only EXECUTE
SQL statements, not PLPGSQL statements. I need to EXECUTE an
assignment statement to accumulate the string of column values.
I have a feeling that I can EXECUTE a CREATE FUNCTION statement,
and then call the function, but this seems over-the-top. I just
want to insert a record into a table! Any better ideas?
-- Simply insert record r into table t.
-- Doesn't work, because EXECUTE takes an SQL command, not
-- a plpgsql statement.
create function insert_record ( record, text ) as '
-- probably ought to pass schema as well as table name, since
-- information_schema.columns query doesn't use search_path.
declare
r as alias for $1;
t as alias for $2;
cr information_schema.columns%rowtype;
first boolean;
column_names text;
column_values text;
begin
first := true;
for cr in select * from information_schema.columns
where table_name=t loop
if not first then
column_names := column_names || '', '';
column_values := column_values || '', '';
first := false;
end if;
column_names := column_names || quote_ident(cr.column_name);
!! execute ''column_values :=
!! column_values || quote_literal(r.'' || cr.column_name || '')'';
end loop;
execute ''insert into '' || t || ''('' || column_names ||
'') values ('' || column_values || '')'';
end;
' language plpgsql;
--Phil.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpgsql - Insert from a record variable?
2004-06-11 14:42 plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-19 06:03 ` Re: plpgsql - Insert from a record variable? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 18:39 ` Re: plpgsql - Insert from a record variable? Tom Lane <tgl@sss.pgh.pa.us>
2004-06-21 17:23 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-21 18:51 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
@ 2004-06-22 00:57 ` basic <basic@mozdev.org>
2004-06-22 14:05 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
0 siblings, 1 reply; 8+ messages in thread
From: basic @ 2004-06-22 00:57 UTC (permalink / raw)
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>; +Cc: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; pgsql-sql
Phil Endecott wrote:
> !! execute ''column_values :=
> !! column_values || quote_literal(r.'' || cr.column_name || '')'';
I'm guessing you want something like
FOR rec IN EXECUTE ''select column_values || quote_literal(r.'' || cr.column_name || '') alias column_values''; LOOP
column_values := rec.column_values;
END LOOP;
another suggestion, rather than do
> if not first then
> column_names := column_names || '', '';
> column_values := column_values || '', '';
> first := false;
> end if;
why not use two arrays and append the name/values using array_append() and use array_to_string() to join them after that?
--
basic
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpgsql - Insert from a record variable?
2004-06-11 14:42 plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-19 06:03 ` Re: plpgsql - Insert from a record variable? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 18:39 ` Re: plpgsql - Insert from a record variable? Tom Lane <tgl@sss.pgh.pa.us>
2004-06-21 17:23 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-21 18:51 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-22 00:57 ` Re: plpgsql - Insert from a record variable? basic <basic@mozdev.org>
@ 2004-06-22 14:05 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
0 siblings, 0 replies; 8+ messages in thread
From: Phil Endecott @ 2004-06-22 14:05 UTC (permalink / raw)
To: basic <basic@mozdev.org>; pgsql-sql
Phil> execute ''column_values :=
Phil> column_values || quote_literal(r.'' || cr.column_name || '')'';
basic> FOR rec IN EXECUTE
basic> ''select column_values || quote_literal(r.'' ||
basic> cr.column_name || '') alias column_values''; LOOP
basic> column_values := rec.column_values;
basic> END LOOP;
I think your code will try to execute a query like this:
select column_values || quote_literal(r.something) alias column_values
This will fail because column_values and r are both plpgsql variables, and so are not visible to the SQL interpreter.
Any other suggestions?
--Phil.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpgsql - Insert from a record variable?
2004-06-11 14:42 plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-19 06:03 ` Re: plpgsql - Insert from a record variable? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 18:39 ` Re: plpgsql - Insert from a record variable? Tom Lane <tgl@sss.pgh.pa.us>
2004-06-21 17:23 ` Re: plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
@ 2004-06-22 01:30 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2004-06-22 01:30 UTC (permalink / raw)
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>; +Cc: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; pgsql-sql
"Phil Endecott" <spam_from_postgresql_sql@chezphil.org> writes:
> I think what I really need is an introspection mechanism
> so that I can loop over each element of the record and construct the
> insert as a string. Maybe this is possible using a different
> server-side language?
pltcl can probably handle this; I'm less sure about plperl or plpython.
(No reflection on the languages, but pltcl has the most complete
Postgres interface.)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-06-22 14:05 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-11 14:42 plpgsql - Insert from a record variable? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-19 06:03 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-19 18:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-21 17:23 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-21 18:51 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-22 00:57 ` basic <basic@mozdev.org>
2004-06-22 14:05 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-22 01:30 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox