By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

sql insert function

P: n/a
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;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
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 AS '
insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
('$1',$2,'$3',$4,$5);
SELECT 1;
' LANGUAGE SQL;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #2

P: n/a
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #3

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #4

P: n/a
"Chris Ochs" <ch***@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #5

P: n/a
On Mon, 12 Jan 2004 16:21:17 -0800 Chris Ochs <ch***@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 rw****@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #6

P: n/a
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" <al**@lintelsys.com.au>
To: <pg***********@postgresql.org>
Sent: Monday, January 12, 2004 4:33 PM
Subject: Re: [GENERAL] sql insert function

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #7

P: n/a
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?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #8

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #9

P: n/a

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 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #10

P: n/a
On Tuesday 13 January 2004 00:35, Doug McNaught wrote:
"Chris Ochs" <ch***@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #11

P: n/a
Please ignore my last post - threading got messed up and my point was already
noted.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #12

P: n/a

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?
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #13

P: n/a
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" <ss****@megazone.bigpanda.com>
To: "Chris Ochs" <ch***@paymentonline.com>
Cc: <pg***********@postgresql.org>
Sent: Tuesday, January 13, 2004 8:36 AM
Subject: Re: [GENERAL] sql insert function


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?

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #14

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.