Re: [despammed] Destination table by variable?

From: Andreas Kretschmer <akretschmer(at)despammed(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: [despammed] Destination table by variable?
Date: 2005-03-19 07:41:16
Message-ID: 20050319074116.GA23644@webserv.wug-glas.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 ===

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Akbar 2005-03-19 11:35:25 error when installing postgresql 8.0.1 in Windows XP
Previous Message Tom Lane 2005-03-19 04:38:38 Re: Question on simulating Enum Data type