From: Sven Willenberger <sven@dmv.com>
To: pgsql-performance@postgresql.org
Subject: Function with table%ROWTYPE globbing
Date: Thu, 03 Nov 2005 10:14:15 -0500
Message-ID: <1131030856.9769.3.camel@lanshark.dmv.com> (raw)
Postgresql 8.0.4 using plpgsql
The basic function is set up as:
CREATE FUNCTION add_data(t_row mytable) RETURNS VOID AS $func$
DECLARE
newtable text;
thesql text;
BEGIN
INSERT INTO newtable thename from mytable where lookup.id =
t_row.id;
thesql := 'INSERT INTO ' || newtable || VALUES (' || t_row.* ')';
EXECUTE thesql;
RETURN;
END;
$func$ LANGUAGE plpgsql VOLATILE;
SELECT add_data(t.*) FROM mytable t where ....
ERROR: column "*" not found in data type mytable
Now I have tried to drop the * but then there is no concatenation
function to join text to a table%ROWTYPE. So my question is how can I
make this dynamic insert statement without listing out every
t_row.colname? Or, alternatively, is there a better way to parse out
each row of a table into subtables based on a column value?
Sven
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sven@dmv.com
Subject: Re: Function with table%ROWTYPE globbing
In-Reply-To: <1131030856.9769.3.camel@lanshark.dmv.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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