pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction with table%ROWTYPE globbing
2+ messages / 2 participants
[nested] [flat]
* Function with table%ROWTYPE globbing
@ 2005-11-03 15:14 Sven Willenberger <sven@dmv.com>
0 siblings, 0 replies; 2+ messages in thread
From: Sven Willenberger @ 2005-11-03 15:14 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Function with table%ROWTYPE globbing
@ 2005-11-03 15:37 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 2+ messages in thread
From: Merlin Moncure @ 2005-11-03 15:37 UTC (permalink / raw)
To: Sven Willenberger <sven@dmv.com>; +Cc: 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?
I don't think it's possible. Rowtypes, etc are not first class yet (on
to do). What I would do is pass the table name, where clause, etc into
the add_data function and rewrite as insert...select and do the whole
thing in one operation.
Merlin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-11-03 15:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-03 15:14 Function with table%ROWTYPE globbing Sven Willenberger <sven@dmv.com>
2005-11-03 15:37 Re: Function with table%ROWTYPE globbing Merlin Moncure <merlin.moncure@rcsonline.com>
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