Thread: Transaction with in function
I looking for solution for commit, rollback with in function.
Below is Example function
CREATE OR REPLACE FUNCTION test()
RETURNS void AS
$BODY$
BEGIN
--- set of statements
Commit;
-- set of statement
commit;
-- if things goes wrong
rollback
RETURN;
END; $BODY$
LANGUAGE 'plpgsql';
Thanks in advance .
Regards
Ravi Katkar
In response to Ravi Katkar : > > > I looking for solution for commit, rollback with in function. You can't use transactions within functions, use savepoints instead. http://www.postgresql.org/docs/current/static/sql-savepoint.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
How can we achieve nested transactions? ( may be using save points )with in functions. Is there any work around? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of A. Kretschmer Sent: Wednesday, May 26, 2010 10:44 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Transaction with in function In response to Ravi Katkar : > > > I looking for solution for commit, rollback with in function. You can't use transactions within functions, use savepoints instead. http://www.postgresql.org/docs/current/static/sql-savepoint.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Wed, May 26, 2010 at 2:52 AM, Ravi Katkar <Ravi.Katkar@infor.com> wrote: > How can we achieve nested transactions? ( may be using save points )with in functions. > Is there any work around? It unfortunately can't be done from within the database. There is only one workaround -- using dblink or similar technology to connect back to the database inside your function to bend/break transaction rules. In the 9.0 world, this (no implicit transaction state pl code, possibly termed 'stored procedures') is one of the top two or three requested features. merlin
Hi ,
I have below function adf with inout, out parameter ,
CREATE OR REPLACE FUNCTION adf(inout voutvar integer , out vVar integer)
AS
$BODY$
BEGIN
voutvar := 20;
vvar := 10;
RETURN;
END; $BODY$
LANGUAGE 'plpgsql'
After compiling I get below signature of function
adf(integer)
and return type as record.
CREATE OR REPLACE FUNCTION adf(INOUT voutvar integer, OUT vvar integer)
RETURNS record AS
I wanted to catch output parameter – Vvar .
Below function tt , tries adf,
CREATE OR REPLACE FUNCTION tt()
RETURNS VOID AS
$BODY$
DECLARE
ii integer;
vout integer;
BEGIN
--vvar := 10;
vout := 10;
perform adf(vout) ;
RETURN;
END; $BODY$
LANGUAGE 'plpgsql';
I have a couple of questions on above function
1) Why the return type is record after compiling?
2) How to catch the return value of out parameter for above case value of vVar.
Thanks,
Ravi Katkar
Hello 2010/6/4 Ravi Katkar <Ravi.Katkar@infor.com>: > Hi , > > > > I have below function adf with inout, out parameter , > > > > CREATE OR REPLACE FUNCTION adf(inout voutvar integer , out vVar integer) > > AS > > $BODY$ > > BEGIN > > voutvar := 20; > > vvar := 10; > > RETURN; > > END; $BODY$ > > LANGUAGE 'plpgsql' > > > > After compiling I get below signature of function > PostgreSQL doesn't compile PLpgSQL code - just validate syntax and store source code and interface description to pg_proc table. When function returns only one parameter, it returns some scalar data type. The syntax isn't important. In other cases function has to return record. One OUT param and one INOUT params are two OUT params -> function has to return record. Second important rule - PostgreSQL can not pass values by ref. Just only by val. So if you want get some result, you cannot use PERFORM statement. CREATE OR REPLACE FUNCTION foo(IN a int, OUT b int) AS $$ BEGIN b := a + 10; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; ... DECLARE result int; BEGIN result := foo(10); END; ... CREATE OR REPLACE FUNCTION foo2(OUT a int, OUT b int, c int) AS $$ BEGIN a := c + 1; b := c + 2; END $$ LANGUAGE plpgsql; DECLARE result RECORD; BEGIN result := foo2(20); RAISE NOTICE 'result is % %', result.a, result.b; END; Regards Pavel Stehule > > > adf(integer) > > > > and return type as record. > > > > CREATE OR REPLACE FUNCTION adf(INOUT voutvar integer, OUT vvar integer) > > RETURNS record AS > > > > I wanted to catch output parameter – Vvar . > > > > Below function tt , tries adf, > > > > CREATE OR REPLACE FUNCTION tt() > > RETURNS VOID AS > > $BODY$ > > DECLARE > > ii integer; > > vout integer; > > BEGIN > > --vvar := 10; > > vout := 10; > > perform adf(vout) ; > > RETURN; > > END; $BODY$ > > LANGUAGE 'plpgsql'; > > > > > > I have a couple of questions on above function > > > > 1) Why the return type is record after compiling? > > 2) How to catch the return value of out parameter for above case value of > vVar. > > > > > > Thanks, > > Ravi Katkar > >
Hi List,
I am trying to execute a function which returns a refcursor, from ODBC client,
But ODBC client could not able to recognize the refcursor hence could not able to retrieve the resultset.
I am working with Posgresql 8.4 version.
Do ODBC - Posgresql supports refcursor?
Do we have user guide or document on ODBC guide which can help.
Thanks,
Ravi Katkar
Hi List,
I need a small help regarding the password options available with PGSQL, I found POSTGRE SQL has –W and –password options available which is prompting for the password. But I want to take the password thru command line argument and keep the password in a variable. Is there any option which will help me to take the password and used in the below way so that it will connect to the DB without asking password again.
psql -h %server% -U %username% -d %database% -p %port% --file File.sql >> xx
Please suggest if there is any option to use the %password% variable using some of the options avail with PSQL.
Thanks,
Ravi Katkar
In response to Ravi Katkar : > > > Hi List, > > > > I need a small help regarding the password options available with PGSQL, I > found POSTGRE SQL has ?W and ?password options available which is prompting for > the password. But I want to take the password thru command line argument and > keep the password in a variable. Is there any option which will help me to take > the password and used in the below way so that it will connect to the DB > without asking password again. http://www.postgresql.org/docs/current/static/libpq-pgpass.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
Hi List,
I need a small help regarding the password options available with PGSQL, I found POSTGRE SQL has –W and –password options available which is prompting for the password. But I want to take the password thru command line argument and keep the password in a variable. Is there any option which will help me to take the password and used in the below way so that it will connect to the DB without asking password again.
psql -h %server% -U %username% -d %database% -p %port% --file File.sql >> xx
Please suggest if there is any option to use the %password% variable using some of the options avail with PSQL.
Thanks,
Ravi Katkar
On 23/06/10 7:56 AM, Ravi Katkar wrote: > > Hi List, > > I need a small help regarding the password options available with > PGSQL, I found POSTGRE SQL has *–W* and *–password* options available > which is prompting for the password. But I want to take the password > thru command line argument and keep the password in a variable. Is > there any option which will help me to take the password and used in > the below way so that it will connect to the DB without asking > password again. > > *psql -h %server% -U %username% -d %database% -p %port% --file > File.sql >> xx* > > * * > > *Please suggest if there is any option to use the %password% variable > using some of the options avail with PSQL.* > > Thanks, > > Ravi Katkar > set PGPASSWORD Variable. Then, psql will not prompt for password. It will accept password from that varaible. Following is an example. set PGPASSWORD=postgres psql -h %server% -U %username% -d %database% -p %port% --file File.sql >> xx -- Thanks& Regards, Vibhor Kumar. Senior System Engineer. EnterpriseDB Corporation The Enterprise Postgres Company Office: 732-331-1300 Ext-2022 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com Follow us on Twitter: http://www.twitter.com/enterprisedb
Hi List,
When I have created Global temporary table its created under/in PG_temp_1 schema by default,
When tried to specify the schema name explicitly its throwing below error
ERROR: temporary tables cannot specify a schema name
I wanted to create a Global temporary table in public or the schema I wish.
Any ideas?
Thanks in advance.
-- Ravi Katkar
2010/6/24 Ravi Katkar <Ravi.Katkar@infor.com>: > Hi List, > > > > When I have created Global temporary table its created under/in PG_temp_1 > schema by default, > > When tried to specify the schema name explicitly its throwing below error > > > > ERROR: temporary tables cannot specify a schema name > > > > I wanted to create a Global temporary table in public or the schema I wish. > > > > Any ideas? PostgreSQL doesn't support global temporary tables yet. Regards Pavel Stehule > > > > Thanks in advance. > > > > > > -- Ravi Katkar