From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | "Sven Willenberger" <sven(at)dmv(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Function with table%ROWTYPE globbing |
Date: | 2005-11-03 15:37:16 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB3417DD796@Herge.rcsinc.local |
Views: | Raw Message | Whole Thread | 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?
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Lamb | 2005-11-03 15:41:14 | Re: Sorted union |
Previous Message | Sven Willenberger | 2005-11-03 15:14:15 | Function with table%ROWTYPE globbing |