Обсуждение: Function with table%ROWTYPE globbing

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

> 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