am 03.02.2006, um 12:58:19 +0100 mailte Arnaud Lesauvage folgendes:
Hi list !
I read in the manual that "Functions and trigger procedures are always
executed within a transaction established by an outer query".
Should I understand, that there is no way to make complex procedures that
would create tables AND fill them with data ?
I have a function that is defined as :
CREATE OR REPLACE FUNCTION my_function() RETURNS VOID AS $$
SELECT create_graph_tables('graph', 'int8');
UPDATE graph_edges SET cost=1;
$$ LANGUAGE SQL;
The create_graph_tables() function creates a table "graph_edges", but when
I run this 'CREATE FUNCTION' statement, I have an error 'relation
"graph_edges" does not exist".
You should better use 'perform' instead 'select' in this function,
and execute 'UPDATE graph_e ...';
I have a simple example:
create or replace function foo (varchar) returns int as $$
begin
execute 'create table ' || $1 || '(id int, cost int);';
return 1;
end;
$$ language plpgsql;
create or replace function foo2 () returns void as $$
begin
perform foo('graph');
execute 'insert into graph values (1,10);';
execute 'UPDATE graph SET cost=1;';
end;
$$ language plpgsql;
test=# \d graph;
Did not find any relation named "graph".
test=# select foo2();
foo2
------
(1 row)
test=# select * from graph ;
id | cost
----+------
1 | 1
(1 row)
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C
http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
