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: sta
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
> Maybe a better example of my problem is with records throughout the system
> like invoices, customer data, etc... If any of these items use a sequence
> and that sequence is global to the table in the database and the number is
> exposed externall
"Jim Wilson" <[EMAIL PROTECTED]> writes:
> Yes, I think you are correct on that. I was misreading column reference for
> column name. Would it be difficult to patch my local copy to either permit
> this or strip off the characters from the qualifier portion in the parser?
[shrug...] You could p
Kragen Sitaker <[EMAIL PROTECTED]> writes:
> On Mon, Jan 12, 2004 at 06:20:23PM -0500, Tom Lane wrote:
>> No; an OID collision would have occurred when you tried to create a
>> table. If two tables are present in pg_class then they have different
>> OIDs, and shouldn't have any conflicts in pg_sta
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 t
they can try to look up information on other customers by doing:
http://domain.com/application/load_record.html?customer_id=12346
http://domain.com/application/load_record.html?customer_id=12344
...basically walking the sequence. Sure, you will protect against this
to happen. NOW
Now my function is created ... Thanks guys ..
Now getting error while trying to execute it
postgres=# select loadme();
WARNING: Error occurred while executing PL/pgSQL function loadme
WARNING: line 7 at SQL statement
ERROR: SPI_prepare() failed on "commit"
Thx again
Deep
PS: here is fn def
Tom Lane <[EMAIL PROTECTED]> said:
> "Jim Wilson" <[EMAIL PROTECTED]> writes:
> > As far as I can tell, there isn't a way to get postgresql to accept column
> > qualifiers (e.g. tablenames). A 'parse error at or near "."' gets returned.
>
> You're either very confused or using a *very* old versi
Stephan Szabo <[EMAIL PROTECTED]> said:
> On Mon, 12 Jan 2004, Tom Lane wrote:
>
> > "Jim Wilson" <[EMAIL PROTECTED]> writes:
> > > As far as I can tell, there isn't a way to get postgresql to accept column
> > > qualifiers (e.g. tablenames). A 'parse error at or near "."' gets returned.
> >
> >
--On Monday, January 12, 2004 17:24:14 -0800 "Thapliyal, Deepak"
<[EMAIL PROTECTED]> wrote:
Made the change and used returns in both places now .. Gives me error
postgres=# \i a.sql
psql:a.sql:10: ERROR: language "plpgsql" does not exist
createlang plpgsql
Any help is appreciated
Thx
Deep
Made the change and used returns in both places now .. Gives me error
postgres=# \i a.sql
psql:a.sql:10: ERROR: language "plpgsql" does not exist
Any help is appreciated
Thx
Deep
create function loadme() returns text as '
Declare
s_out text ;
Begin
For i in 1..1 loop
insert into
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 d
Thapliyal, Deepak wrote:
create function loadme() return text as '
try "RETURNS" instead of "RETURN"
[the guys writing the function parser might want to consider reporting
what the parser was expecting at this point]
Declare
s_out text ;
Begin
For i in 1..1 loop
insert into test val
Thx to doug ... Old error is gone .. But I am now getting new error
postgres=# \i a.sql
psql:a.sql:10: ERROR: language "plpgsql" does not exist
But I verified that I setup plpgsql
[EMAIL PROTECTED] createlang -d test -l
Procedural languages
Name | Trusted?
-+--
plpgsql | t
"Thapliyal, Deepak" <[EMAIL PROTECTED]> writes:
> I am getting this error
>
> postgres=# \i a.sql
> psql:a.sql:10: ERROR: parser: parse error at or near "return" at character
> 26
> create function loadme() return text as '
^^
I think you want "returns" here.
-D
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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, January 12, 2004 4:33 PM
Subject: Re: [GENERAL] sql insert fu
I am getting this error
postgres=# \i a.sql
psql:a.sql:10: ERROR: parser: parse error at or near "return" at character
26
--
a.sql
--
create function loadme() return text as '
Declare
s_out text ;
Begin
For i in 1..1 loop
insert into test values (i,''Test'');
end loop;
return s_
On Mon, 12 Jan 2004 16:21:17 -0800 Chris Ochs <[EMAIL PROTECTED]> 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
"Chris Ochs" <[EMAIL PROTECTED]> 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
>
>
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)--
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 '
First I created a function that selected the next available pin
code from a table of pre-defined pin codes:
CREATE FUNCTION "public"."get_next_pin_code" () RETURNS varchar AS'
DECLARE
my_pin_code VARCHAR;
BEGIN
...
/* this is the pincode we just fetched */
RETUR
On Mon, 12 Jan 2004, Richard Huxton wrote:
> On Sunday 11 January 2004 09:29, Dario Ottaviano wrote:
> > I use postgres on a window server (IIS 5.1)
> > Is there anybody that knows if is possible to make postgres no case
> > sensitive in the manipulating data into tables/views?
>
> There's no g
Yes, I did. For just the simple updating, (not the
logging you are doing) NEW is what you want. But OLD is proper
for archiving/logging.
--elein
On Mon, Jan 12, 2004 at 08:22:27PM +0700, Chris Travers wrote:
> Hi Elein;
>
> Nope, OLD is correct. I track the OLD values and then use the view to
Tom Lane wrote:
Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID. The hash will add no uniqueness that was
not there before.
The cool thing about a 'GUID' (or in my example a hashed sequence number
[sure
toss in some entropy if you want it]
--- [EMAIL PROTECTED] wrote:
> I write this to tell you why we won't use postgresql
> even though we wish we
> could at a large company. Don't get me wrong I love
> postgresql in many
> ways and for many reasons , but fact is fact. If
> you need more detail I
> can be glad to prove all my points.
On Sat, 10 Jan 2004, Eric Freeman wrote:
Is there any way in Postgres to SELECT a list of table names from inside of
a C program using ECPG?
Something similar to SELECT current_user that will give you all of the
tables in the database you are connected to.
Not sure if this is what you're tryi
_
High-speed usersbe more efficient online with the new MSN Premium Internet
Software. http://join.msn.com/?pgmarket=en-us&page=byoa/prem&ST=1
---(end of broadcast)---
TIP 4: Don't '
On Sat, 10 Jan 2004, Eric Freeman wrote:
> Is there any way in Postgres to SELECT a list of table names from inside of
> a C program using ECPG?
> Something similar to SELECT current_user that will give you all of the
> tables in the database you are connected to.
If you are running 7.4 and hav
unsubscribe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Tom Lane wrote:
David Garamond <[EMAIL PROTECTED]> writes:
Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a
unique seed like MD5 of '/sbin/ifconfig' output)...
Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID. The hash
On Sun, Jan 11, 2004 at 21:53:09 +0700,
David Garamond <[EMAIL PROTECTED]> wrote:
>
> My concern is that, the PostgreSQL docs says NUMERIC & DECIMAL is very
> slow compared to INT/BIGINT. Should I worry about that?
Most likely disk IO not cpu will be your bottleneck and the extra overhead
of n
hi,
I have done work on sourceforge for my c++ cgi library. It is at
http://sourceforge.net/projects/cgiutils2/
It is built up of 3 separate libraries, the cgi library which parses post requests and
gets form values and such, a session library, and a template library which uses a
custom parse
David Garamond <[EMAIL PROTECTED]> writes:
> Perhaps I can make a GUID by MD5( two random numbers || a timestamp || a
> unique seed like MD5 of '/sbin/ifconfig' output)...
Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID. The hash will add no
Richard Huxton <[EMAIL PROTECTED]> writes:
> On Monday 12 January 2004 05:57, [EMAIL PROTECTED] wrote:
>> i have a problem with insertion data and running post insert trigger
>> on it.
> Better post the CREATE TABLE, trigger code and a sample INSERT.
And the specific error messages you're getting
I couldn't agree with you more. I'm just a developer in a very large
company and getting anyone to listen and then understand that logic would
be a nightmare to say the least. If it was my company I would put money
toward those issues.
D. Dante Lorenso wrote:
GUID? Isn't that really nothing more than an MD5 on a sequence?
SELECT (MD5(NEXTVAL('my_table_seq'))) AS my_guid;
I know there are several algorithms to generate GUID, but this is
certainly inadequate :-) You need to make sure that the generated GUID
will be unique th
Hi Elein;
Nope, OLD is correct. I track the OLD values and then use the view to
combine those with the current ones. This allows the OLAP portions of the
code to hit against *all* the data, while archiving old, outdated
information in the archive table. It also allows deleted tuples to be
track
Dear Anton Nikiforov,
The problem:
From the very beginning everything was fine and all records that i
was getting from routers were calculated just right. I spent a weeks
monitoring and testing my software.
Now i have 10.000.000 records in raw table and when i'm inserting data
alot of records are
Hi!
Have a problem, probably easy to solve... I want to dump a database
which resides on my local server with another, and not existing, owner
than the one who actually owns it locally.
The beginning of the dump file looks like:
\connect - postgres
SET search_path = public, pg_catalog
2004-01-10 kl. 00.21 skrev Richard Huxton:
Can't get the
sudo -u user1 /usr/local/bin/pg_dump db1 | /usr/local/bin/psql -U
user2
-h host2 db2
to work.
Only thing that happens is that I get multiple passwordprompts, and
then I gets told that the password is incorrect…
You probably want a .pgpas
On Monday 12 January 2004 05:57, [EMAIL PROTECTED] wrote:
> Dear all,
> i have a problem with insertion data and running post insert trigger
> on it.
> When i'm doing tests - everything is going well, but in
> production when multiple records being inserted losses happend.
OK - there must be some
On Sunday 11 January 2004 22:05, D. Dante Lorenso wrote:
> David Garamond wrote:
> > Are there any drawbacks of using BYTEA for PK compared to using a
> > primitive/atomic data types like INT/SERIAL? (like significant
> > performance hit, peculiar FK behaviour, etc).
> >
> > I plan to use BYTEA for
On Sunday 11 January 2004 09:29, Dario Ottaviano wrote:
> I use postgres on a window server (IIS 5.1)
> Is there anybody that knows if is possible to make postgres no case
> sensitive in the manipulating data into tables/views?
There's no general "case_sensitive = yes/no" flag. There are case-in
On Sunday 11 January 2004 13:14, Nigel J. Andrews wrote:
> On Sun, 11 Jan 2004, Richard Huxton wrote:
> > On Saturday 10 January 2004 21:31, D. Dante Lorenso wrote:
> > > I just ran into a dump/restore problem with a bigserial column
> > > on a renamed table.
> >
> > [snip]
> >
> > > I've corrected
Thanks Graeme!
MG> http://marc.theaimsgroup.com/?l=postgresql-sql&m=106739176106877&w=2
But this function is still returning only a subtree and in addition it
have a bug when calling it like
SELECT * FROM crawl_tree(0,0);
You will always get ERROR: out of memory
But this function is clear enough t
46 matches
Mail list logo
