Hello pgsql-general,
I have two questions about PostgreSQL and PL/pgSQL.
1. PostgreSQL did not support "ALTER TABLE ... DROP COLUMN".
You propose using "CREATE TABLE temp AS SELECT ..."
But I need execute it as dynamic query in function, because I did
not know all attributes beforehand.
I try to use "EXECUTE 'CREATE TABLE temp AS SELECT '||attributes||' FROM
'||tablename".
When I execute my function, PostgreSQL generate error "EXECUTE of SELECT ... INTO is not implemented yet".
Query was "CREATE TABLE temp AS SELECT id, name FROM test".
What will you recommend me?
2. How to get value of dynamic attribute in trigger?
It is my table:
CREATE TABLE test(
id integer,
name varchar,
... -- I did not know all attributes beforehand.
PRIMARY KEY(id)
);
Also I have a trigger on it table:
CREATE FUNCTION test_trigger() RETURNS OPAQUE AS'
DECLARE
attname varchar;
BEGIN
attname:=''id'';
-- How to get value of attribute of "NEW::record" and "OLD::record".
-- Name of interested attribute is in variable "attname"
-- I need something like this "new.$attname" to get new.id
-- if attname:='name' then new.name etc.
END;' LANGUAGE 'plpgsql';
CREATE TRIGGER after_update_test AFTER UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE test_trigger();
Thank you very much in advance.
--
Best regards,
Kopylov mailto:kopylov@omnicom.ru