From: | "Erik Dahlstrand" <df03daer(at)ing(dot)hj(dot)se> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: [despammed] Destination table by variable? |
Date: | 2005-03-18 19:11:54 |
Message-ID: | s23b3616.034@gw-smtp.hj.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
** High Priority **
Thank you all for the replies...
However, I can't get the EXECUTE statement to work... I think it has something to do with the quotation marks, any suggestions?
CREATE OR REPLACE FUNCTION insert_object(_category_id int4, _header "varchar", _description "varchar") RETURNS int4 AS
DECLARE
destTable text;
insertString text;
id int4;
BEGIN
SELECT INTO destTable "category".table FROM category WHERE id = _category_id;
insertString := ''INSERT INTO '' || destTable || '' (category_id, header, description, created_on) VALUES (''
|| _category_id || '',''
|| _header || '',''
|| _description || '',''
|| DEFAULT || '');'';
EXECUTE insertString;
id := currval('object_id_seq');
RETURN id;
END;
>>> Andreas Kretschmer <akretschmer(at)despammed(dot)com> 03/18/05 4:45 PM >>>
am 18.03.2005, um 15:35:37 +0100 mailte Erik Dahlstrand folgendes:
> Hi!
>
> I want to choose the destination table by a variable. Something like this:
>
> CREATE FUNCTION insert_this(int4, varchar, varchar) RETURNS void AS $$
> DECLARE
> destTable text;
> BEGIN
> SELECT INTO destTable get_table_name($1);
>
> INSERT INTO destTable VALUES ($2, $3);
> END;
> $$ LANGUAGE plpgsql;
>
> Is it possible?
Yes, you can build your SQL as string and then execute this with
EXECUTE.
http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-03-18 19:27:56 | Re: Array of Objects |
Previous Message | Tom Lane | 2005-03-18 18:26:03 | Re: Multidimensional arrays in plpgsql |