| From: | Sven Willenberger <sven(at)dmv(dot)com> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Function with table%ROWTYPE globbing |
| Date: | 2005-11-03 15:14:15 |
| Message-ID: | 1131030856.9769.3.camel@lanshark.dmv.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Merlin Moncure | 2005-11-03 15:37:16 | Re: Function with table%ROWTYPE globbing |
| Previous Message | Merlin Moncure | 2005-11-03 14:01:52 | Re: [PERFORM] improvise callbacks in plpgsql |