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 14 12984
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
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
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
"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
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
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
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
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
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
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jason |
last post by:
How does one loop through the contents of a form complicated by dynamic
construction of checkboxes which are assigned a 'model' and 'listingID' to
the NAME field on the fly in this syntax:...
|
by: le_mo_mo |
last post by:
Hi,
I am trying to insert some data using FormView which works fine but I
do not know how to include hiddenfield and bind them in the construct
so I can insert information like username, date,...
|
by: neilcancer |
last post by:
i wrote a function to insert an elem into a list, but it was
wrong,wrong,wrong! and i have no idea about why it was wrong. If anyone
know, leave your advice, thank you.
#include<stdio.h>...
|
by: riley.derrick |
last post by:
Hello All
I have been looking over the various forums and threads trying to learn
as much as I can. Here is my issue. I am trying to do the following:
Write a function that involves the...
|
by: flash |
last post by:
write a program that manipulates arrays of integers. The main program should call three functions: Insert, Delete, and Search. The Insert function should call a function Sort that sorts the array.
...
|
by: askmatlab |
last post by:
Hello all:
I would like to insert a number into a linked list in ascending order.
Is the following function correct?
void insert(Node **node, int v)
{
Node *tmp = (Node...
|
by: choonmui |
last post by:
hello.. i am new here and need some help in vb.net!!
i need to do an insert function.. for example: there are some checkboxes. if the box is checked and a user clicks the button 'select', the item...
|
by: Amit Bhatia |
last post by:
Hi,
I have defined something like the following in tree.h file:
//everything else including using namespace __gnu_cxx;
typedef hash_map<pair<int,int>, Qd_Node, Qd_Node_HasherLoc_Tree;...
|
by: subramanian100in |
last post by:
Suppose I have a vector<Tor deque<Tfor some type T.
Suppose vector<T>::iterator iter is an iterator.
Then consider the operation
vector<T>::iterator nit = container.insert(iter, T());
After...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |