From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Create table and update it within one function ? |
Date: | 2006-02-03 12:24:57 |
Message-ID: | 20060203122457.GD17468@webserv.wug-glas.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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 ===
From | Date | Subject | |
---|---|---|---|
Next Message | Arnaud Lesauvage | 2006-02-03 14:20:10 | Re: Create table and update it within one function ? |
Previous Message | Øyvind Møller Asbjørnsen | 2006-02-03 12:09:03 | How do I interpret the data returned from a DeadLock-Exception?(Java) |