function to update different tables

Поиск
Список
Период
Сортировка
Искать

function to update different tables

От:
"Lars Eckberg" <le@eckbergconsulting.com>
Дата:
Hey all,

Newbie question:

I'm trying to create a function, where the first parameter will be the 
tablename to update. I have tried to escape $1 in every way I can think of, 
but with no luck. I get an error when trying to create the function.

ERROR:  parser: parse error at or near "$1" at character 11

Here is the function:

CREATE FUNCTION reserve_ports(text,smallint,text,text,int) RETURNS smallint AS 
'
  UPDATE $1
    SET status = \'reserved\', vlan_uid = $2, c_uid = $3
    WHERE status = \'free\' AND oid IN (SELECT oid FROM $1 WHERE  status = 
\'free\' AND module_type ILIKE $4 ORDER BY id LIMIT $5);
  SELECT 1;
' LANGUAGE SQL;

I have looked at the manual but i cant seem to find anything covering this.
Do i have to use another approach??

Any examples or hints will be appreciated greatly. =)

Cheers,
--Lars

Re: function to update different tables

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Lars Eckberg"  writes:
> I'm trying to create a function, where the first parameter will be the 
> tablename to update.

There is no way to do that in an SQL-language function.  If you use
plpgsql then it's possible to construct an SQL command as a string
(ie, concatenate the constant parts with the table name) and then
execute it with an EXECUTE statement.

			regards, tom lane
FAQ