Thread: sql insert function
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;
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
>
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
"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
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
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 > 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 >
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
> > 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
>
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 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?
>
>
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