Re: Create table and update it within one function ?

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

Re: Create table and update it within one function ?

От:
"A. Kretschmer" <andreas.kretschmer@schollglas.com>
Дата:
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    ===

Create table and update it within one function ?

От:
Arnaud Lesauvage <thewild@freesurf.fr>
Дата:
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".

Is there no way to do this ?

Thanks a lot !
--
Arnaud

Re: Create table and update it within one function ?

От:
Arnaud Lesauvage <thewild@freesurf.fr>
Дата:
A. Kretschmer a écrit :
 > > You should better use 'perform' instead 'select' in this 
function,
 > > and execute 'UPDATE graph_e ...';

Andreas, it works great !
Thanks for your help *again* !

Regards
--
Arnaud
FAQ