sql insert function

Never mind, I forgot to quote the quote's...

Chris

The documentation doesn't have any examples of using an sql language
function to do an insert, andI am at loss as to I am doing wrong here.
The error I get trying to create the function is: ERROR: syntax error at
or near "$1" at character 148

CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns

integer

Chris Ochs wrote:

CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns integer
AS '
insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('$1',$2,'$3',$4,$5);
SELECT 1;
' LANGUAGE SQL;

try

CREATE FUNCTION taxship (varchar,integer,varchar,float,float) RETURNS
integer AS '
BEGIN
insert into taxship(s_oid,order_id,mer_id,tax,shipping)
values ('$1',$2,'$3',$4,$5);
return 1;
END' LANGUAGE 'plpgsql';

since what you are trying to do is a compound statement.

Chris Ochs wrote:

Never mind, I forgot to quote the quote's...

Heh... and here I was thinking you were trying to build a function ;)

And I made the same mistake as you... guess I should proofread instead
of copy-pasting ;)

Alex Satrapa

On Mon, 12 Jan 2004 16:21:17 -0800 Chris Ochs <chris@paymentonline.com> wrote:

The documentation doesn't have any examples of using an sql language
function to do an insert, andI am at loss as to I am doing wrong here.
The error I get trying to create the function is: ERROR: syntax error at
or near "$1" at character 148

CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns integer
AS '
insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('$1',$2,'$3',$4,$5);
SELECT 1;
' LANGUAGE SQL;

i do believe you need to double up the single quotes inside the
function body, e.g.

(''$1'',$2,''$3'',$4,$5);

otherwise, the quote before the $1 ends up terminating the
function body.

richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

"Chris Ochs" <chris@paymentonline.com> writes:

The documentation doesn't have any examples of using an sql language
function to do an insert, andI am at loss as to I am doing wrong here.
The error I get trying to create the function is: ERROR: syntax error at
or near "$1" at character 148

CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns integer
AS '
insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('$1',$2,'$3',$4,$5);
SELECT 1;
' LANGUAGE SQL;

When you want to use single quotes inside a quoted string (which is
what a function body is) you need to escape them.

-Doug

Hmmm since the function already knows the type, the quotes aren't needed.
If you use them it just inserts a literal $1 and $3.

----- Original Message -----
From: "Alex Satrapa" <alex@lintelsys.com.au>
To: <pgsql-general@postgresql.org>
Sent: Monday, January 12, 2004 4:33 PM
Subject: Re: [GENERAL] sql insert function

I am seeing another strange thing when using a function that does an insert
instead of doing the insert directly. This is using cached connections with
apache/mod_perl.

My program starts a transaction, does about 20 inserts, then commits. When
I replace once of the inserts with a function that does the insert, when I
do the commit I get this message:

WARNING: there is no transaction in progress

The inserts all commit fine. Do functions used through DBD::Pg do something
like turn on autocommit after a function is called?

Chris Ochs wrote:

My program starts a transaction, does about 20 inserts, then commits. When
I replace once of the inserts with a function that does the insert, when I
do the commit I get this message:

WARNING: there is no transaction in progress

The inserts all commit fine. Do functions used through DBD::Pg do something
like turn on autocommit after a function is called?

Is your function calling 'commit' itself? If so, it could be committing
before your SQL statement issues the 'commit', thus attempting to commit
a transaction which doesn't exist any more.

DBD::Pg defaults to "AutoCommit" behaviour, unless you explicitly turn
it off:

my $dbh = DBI->connect (
"DBI:Pg:dbname=database", "user" , "password",
{AutoCommit => 0}
);

HTH
Alex Satrapa

My function does not call commit, and I have autocommit turned off.

In the postgresql server logs it looks like this without using the function:

LOG: statement: begin
LOG: statement: insert into...
LOG: statement: insert into...
LOG: statement: insert into...
LOG:: statement: commit
LOG: statement: begin

With the function it does this:

LOG: statement: begin
LOG: statement: insert into...
LOG: statement:
insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('0000-10000000',10000000,'0000',1,1);
END
CONTEXT: SQL function "taxship" during startup
LOG: statement: insert into...
LOG:: statement: commit
WARNING: there is no transaction in progress
LOG: statement: begin

In both cases all the data gets inserted correctly, but I would like to
know how I could be getting the warning that there is no open transaction.
I am running with autocommit turned off, so it seems there would have to be
a transaction or the data wouldn't get inserted. Either that or there is
something else that is causing the data to commit without an explicit commit
being called? I'm at a loss.

Chris Ochs wrote:

My program starts a transaction, does about 20 inserts, then commits.

When

I replace once of the inserts with a function that does the insert, when

I

do the commit I get this message:

WARNING: there is no transaction in progress

The inserts all commit fine. Do functions used through DBD::Pg do

something

On Tuesday 13 January 2004 00:35, Doug McNaught wrote:

"Chris Ochs" <chris@paymentonline.com> writes:

CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns
integer AS '
insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('$1',$2,'$3',$4,$5);
SELECT 1;
' LANGUAGE SQL;

When you want to use single quotes inside a quoted string (which is
what a function body is) you need to escape them.

Can I point out that you don't need any quotes here - these are variables not
literals. Just do:

INSERT INTO (...) VALUES ($1,$2,$3...)

--
Richard Huxton
Archonet Ltd

Please ignore my last post - threading got messed up and my point was already
noted.

--
Richard Huxton
Archonet Ltd

On Mon, 12 Jan 2004, Chris Ochs wrote:

LOG: statement: begin
LOG: statement: insert into...
LOG: statement:
insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('0000-10000000',10000000,'0000',1,1);
END

Where is that END coming from? Did you accidentally put it in your
function?

Yes it was in my function. I thought the docs said that BEGIN and END had
no effect on transactions though? Plus wouldn't there have to be a
transaction active since I was not using autocommit and the inserts did in
fact commit?

I suspect it is the end statement doing this though, I'll take it out and
see what happens.

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Chris Ochs" <chris@paymentonline.com>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, January 13, 2004 8:36 AM
Subject: Re: [GENERAL] sql insert function

On Tuesday 13 January 2004 17:46, Chris Ochs wrote:

Yes it was in my function. I thought the docs said that BEGIN and END had
no effect on transactions though? Plus wouldn't there have to be a
transaction active since I was not using autocommit and the inserts did in
fact commit?

I suspect it is the end statement doing this though, I'll take it out and
see what happens.

I think you're right - I looked back at your earlier posts and you are mixing
up plpgsql and sql function syntax (easy enough to do).

BEGIN...END bracket the body of a plpgsql function, but control a transaction
in the SQL function. The BEGIN would have been ignored, the END would have
committed the current transaction.

--
Richard Huxton
Archonet Ltd