Thread: Re: [despammed] Destination table by variable?

** 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@despammed.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

am  18.03.2005, um 20:11:54 +0100 mailte Erik Dahlstrand folgendes:
> 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;


Your mistakes:
- you should use quote_ident for destTable in insertString
- you should use quote_literal for header and description


a little example:

,----[  my table  ]
| test_db=# \d info
|          Tabelle »public.info«
|  Spalte |        Typ        | Attribute
| --------+-------------------+-----------
|  id     | integer           |
|  name   | character varying |
`----

,----[  the function  ]
| create or replace function insert_name (integer, varchar) returns text as '
| declare
|         _id alias for $1;
|         _name alias for $2;
|         _my_sql varchar;
|         _res record;
| begin
|         _my_sql = ''insert into info (id, name) values ('' || _id || '', '' || quote_literal(_name) || '');'';
|         RAISE NOTICE ''%'', _my_sql;
|         execute _my_sql;
|         select into _res id, name from info where id = $1;
|         return _res.id || '' '' || _res.name;
| end;
| ' language plpgsql;
`----

,----[  the test  ]
| test_db=# select * from info;
|  id | name
| ----+------
| (0 Zeilen)
|
| test_db=# select insert_name(1, 'Erik Dahlstrand');
| HINWEIS:  insert into info (id, name) values (1, 'Erik Dahlstrand');
|     insert_name
| -------------------
|  1 Erik Dahlstrand
| (1 Zeile)
|
| test_db=# select * from info;
|  id |      name
| ----+-----------------
|   1 | Erik Dahlstrand
| (1 Zeile)
`----


Btw.: i'm reading from top to bottom, and your 'X-Mailer: Novell
GroupWise' breaks the thread...

Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===