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

Search Discussions

  • Andreas Kretschmer at Feb 3, 2006 at 12:24 pm

    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 ===
  • Arnaud Lesauvage at Feb 3, 2006 at 2:20 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 3, '06 at 11:58a
activeFeb 3, '06 at 2:20p
posts3
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase