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

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

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