Function to insert entire row%ROWTYPE into other table

From: Sven Willenberger <sven(at)dmv(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Function to insert entire row%ROWTYPE into other table
Date: 2005-11-02 22:02:16
Message-ID: 1130968936.7893.57.camel@lanshark.dmv.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

SVen

Browse pgsql-general by date

  From Date Subject
Next Message MaXX 2005-11-02 22:03:29 Re: Clustered indexes - When to use them?
Previous Message MaXX 2005-11-02 21:55:36 Re: Clustered indexes - When to use them?