Hello,
I have not Oracle, so I cannot test it, but PostgreSQL implementation
respect Oracle:
http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php
Regards
Pavel Stehule
2007/6/30, Bruno Wolff III [EMAIL PROTECTED]:
The following is just FYI.
I was recently doing some stuff
}'; -- inicialisation need for
versions 8.2.
begin a[4] := 10; a[8] := 10;
raise notice '%', a;
return a[4];
end$$ language plpgsql;
Regards
Pavel Stehule
2007/6/29, Gauthier, Dave [EMAIL PROTECTED]:
How does one implement a simple, general purpose, assignable array (or list)
in pl/pgsql? From what
We pretty much ignore the spec's concept of non-error completion
conditions, but it sounds like Oracle tries to support it.
Anyway, there's no doubt that we can point to the behavior of MAX/MIN
as defense for what we made GREATEST/LEAST do, so I'm inclined to leave
their behavior alone, at
Maybe that reference was for an earlier version of Oracle and the definition
changed at some point? I only have access to version 9 and greatest and
lest are strict there.
I am installing OracleXE and I'll test it.
Pavel
---(end of
You are correct. PostgreSQL implementation isn't compatible with
Oracle. It's question if the behave can be changed now.
Pavel
2007/6/30, paul rivers [EMAIL PROTECTED]:
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Pavel Stehule
Hello
look on http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html
Pavel Stehule
2007/7/2, Ashish Karalkar [EMAIL PROTECTED]:
Hello All,
I want to create a user through script and dont want user to assign password
interactively, I want it to be assigned through some file
password
interactively, I want it to be assigned through some file or anything
else
some this like
createuser -P * OR createuser -P file
Is there any way?
From: Pavel Stehule [EMAIL PROTECTED]
To: Ashish Karalkar [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Sent: Monday, July 02
The query would become
SELECT a.id, a.name, array_acc(b.name) as b_names
FROM a LEFT JOIN b USING(id)
GROUP BY a.id, a.name;
All variants are possible. Variant with array_to_string is faster and
doesn't need own aggregate function. And you can easy remove
duplicities.
SELECT a.id, a.name,
)::at]);
INSERT 0 1
postgres=# SELECT * FROM foo;
a
---
{(10,20)}
{(10,20),(20,30)}
(2 rows)
postgres=# SELECT a[1] FROM foo;
a
-
(10,20)
(10,20)
(2 rows)
postgres=# SELECT a[1].a FROM foo;
a
10
10
(2 rows)
regards
Pavel Stehule
2007/7/7, Chris
Hello
I have similar problem with vacuum on 8.1
I have 256M table. pgstattuple reports 128M free. I stopped vacuum
after 1hour (maintenance_work_mem = 160M). I had not more time.
Regards
Pavel Stehule
2007/7/10, Tom Lane [EMAIL PROTECTED]:
Brad Nicholson [EMAIL PROTECTED] writes:
On Tue
% comparable, but it is one from
more arguments for upgrade.
Regards
Pavel Stehule
2007/7/10, Tom Lane [EMAIL PROTECTED]:
Brad Nicholson [EMAIL PROTECTED] writes:
On Tue, 2007-07-10 at 11:19 -0400, Tom Lane wrote:
Oh, I forgot to mention --- you did check that vacuum_mem is set to
a pretty high
+
10 | 20
(1 row)
Regards
Pavel Stehule
2007/7/17, Zlatko Matić [EMAIL PROTECTED]:
I have a plpgsql function that returns dataset.
First it was defined to return SETOF someview.
Then I changed it to return SETOF RECORD, in order to be able to return
dataset with varying number of columns.
But, I
Hello
http://www.bitboost.com/ref/international-address-formats.html
http://www.census.gov/geo/www/standards/scdd/AddressStandardV2_April%2017_2003.htm
Rock solid solution will be propably too heavy. Propably you can find
some more national specific sources.
Regards
Pavel
2007/7/19, Bruno
automatically. Second and others
clusters have to be created manually with statement initdb. These
clusters will share only dll files. If you wont absolutely independent
installations, then change paths and ports.
Regards
Pavel Stehule
2007/7/20, Zlatko Matić [EMAIL PROTECTED]:
Hi.
If I understood
Hello
I don't understand well, what you want to do. You can
cat myfile.txt | psql database
or like your sample
for i in `cat myfile.txt` ; do psql mydatabase -c insert into
mytable (aaa,bbb) values (xxx, yyy);
...
regards
Pavel Stehule
2007/7/23, Chuck Payne [EMAIL PROTECTED]:
Hey,
I
/sql-createtable.html
http://www.jdbc-tutorial.com/jdbc-create-table.html
Regards
Pavel Stehule
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Hello,
send source code please, we cannot check any without source.
Regards
Pavel Stehule
2007/7/25, Anton A. Patrushev [EMAIL PROTECTED]:
Hello,
My C function for Pl/pgsql worked fine until I upgraded to 8.2.4
version. Now it fails with signal 11 (segmentation fault).
The problem is - I
;
' LANGUAGE plpgsql;
I am sorry for czech variable names
Regards
Pavel Stehule
2007/8/2, Gregory Williamson [EMAIL PROTECTED]:
I am not sure if this is the appropriate list -- please point me at the
correct one if not.
I'm trying to create a procedure that would let me retrieve a list of
tables
Hello
I found strange postgresql's behave. Can somebody explain it?
Regards
Pavel Stehule
CREATE TABLE users (
id integer NOT NULL,
name VARCHAR NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO users VALUES (1, 'Jozko');
INSERT INTO users VALUES (2, 'Ferko');
INSERT INTO users VALUES (3, 'Samko
2007/8/11, Gregory Stark [EMAIL PROTECTED]:
Pavel Stehule [EMAIL PROTECTED] writes:
checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,
CONTEXT: SQL statement UPDATE ONLY public.tasks SET worker =
NULL WHERE $1 OPERATOR(pg_catalog.=) worker
This says you
2007/8/11, Gregory Stark [EMAIL PROTECTED]:
Sorry, I reread your original post. My initial reading was wrong.
To make this work I think you'll need to set these constraints to be deferred.
--
it works with deferred constraints . It's strange, it works with two
columns but dowsn't work with
2007/8/11, Stephan Szabo [EMAIL PROTECTED]:
On Sat, 11 Aug 2007, Pavel Stehule wrote:
Hello
I found strange postgresql's behave. Can somebody explain it?
There's a bug since it should work for any number, but we've likely missed
something. I'm not sure why 2 references work, as I'd
because the documentation
said the copy commands were much faster, now I am regretting it. How
can I get psql to interpret the dumpfile correctly and restore my
data?
Thanks.
Hello
try pg_restore myfile
Regards
Pavel Stehule
---(end of broadcast
random and you cannot count with it.
Regards
Pavel Stehule
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
hash = (text *)palloc(hlen+1);
??? palloc(hlen + VARHDRSZ)
memset(VARDATA(hash), 0, hlen);
SHA512(VARDATA(plain), hlen, VARDATA(hash));
++ VARATT_SIZEP (hash) = VARHDRSZ + ;
PG_RETURN_TEXT_P(hash);
}
---(end of
.
PostgreSQL BLOB implementation is well. We used it without any
problems with images from 20K-30M.
Regards
Pavel Stehule
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Hello,
I am sorry, this mail had to be send only to pgsql-general
nice a day
Pavel Stehule
-- Forwarded message --
From: Pavel Stehule [EMAIL PROTECTED]
Date: 15.8.2007 8:01
Subject: is this trigger safe and efective? - locking (caching via triiggers)
To: PostgreSQL Hackers
2007/8/15, Erik Jones [EMAIL PROTECTED]:
On Aug 15, 2007, at 4:57 AM, Pavel Stehule wrote:
I write sample about triggers and i have question. is my solution
correct and exists better solution?
Regards
Pavel Stehule
DROP SCHEMA safecache CASCADE;
CREATE SCHEMA safecache
Hello
look on
see http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dblink/
or http://pgfoundry.org/projects/dblink-tds/
Regards
Pavel Stehule
2007/8/16, Phoenix Kiula [EMAIL PROTECTED]:
Probably an optimistic question - can a user with access to two
databases create a function in one
own stats but I was
wondering is there are any good tools to automate this.
Specifically working with pl/pgSQL and 8.2 (on Linux or windows) if
that helps.
Thanks,
LewisC
pgfouine is the best
Regards
Pavel Stehule
---(end of broadcast
Hello
it isn't bug!
PostgreSQL's driver for perl
http://search.cpan.org/~dbdpg/DBD-Pg-1.49/Pg.pm
look to postgresql.conf (port) and pg_hba.conf (enable access)
Regards
Pavel Stehule
2007/8/17, rakesh kumar [EMAIL PROTECTED]:
how to connect postgresql database with perl
Please if server
way instead of
relational.
It is solved. It was PostgreSQL bug repaired
http://archives.postgresql.org/pgsql-committers/2007-08/msg00207.php
Thank you
nice a day
Pavel Stehule
---(end of broadcast)---
TIP 5: don't forget to increase your free space
into a database. You can write your unit
tests as scripts and provide them to pgUnitTest. It will execute the
unit tests and will generate a report.
This is a first beta version.
Regards
Pavel Stehule
---(end of broadcast)---
TIP 3: Have you checked
Hello
2007/8/21, Josh Trutwin [EMAIL PROTECTED]:
Hi - I have the following array field:
SELECT pb_ids FROM pb WHERE id = 123:
pb_id
---
{196,213,215,229,409}
These numbers map to a productid in tblproducts so I figured I could
do this:
SELECT *
FROM
2007/8/21, Josh Trutwin [EMAIL PROTECTED]:
On Tue, 21 Aug 2007 20:15:59 +0200
Pavel Stehule [EMAIL PROTECTED] wrote:
SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123)
or
SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb
WHERE id=123))
Thanks
notice '%', rc; end; $$ language plpgsql;
postgres=# select f();
NOTICE: 2 f
---
(1 row)
Regards
Pavel Stehule
2007/9/9, Robert Fitzpatrick [EMAIL PROTECTED]:
I have a trigger function that I want to apply to several tables, hence
my use of TG_RELNAME. I just want the record to get inserted
according to his ability. To each according to his ability to
litigate.
sample:postgres=# select extract(epoch from time '10:01:30' - time '10:00:00');
date_part
---
90
(1 row)
Regards
Pavel Stehule
---(end of broadcast)---
TIP 3: Have
? Or am I wishing for the sky? ;-)
Thanks for any help you can provide.
JDG
On stored procedure level you can use dbms_alert functionality from
orafce library.
http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29#DBMS_ALERT
Pavel Stehule
---(end of broadcast
+
2007-09-14 22:29:58.285+02 | select now(), current_statement();
(1 row)
Regards
Pavel Stehule
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards
Pavel Stehule
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Hello Rhys
its not mystery
create or replace function mysrf(a integer)
returns integer
as $$
begin
for i in 1..a loop
return next i; -- important
end loop;
return;
end;
$$ language plpgsql;
you can call select * from mysrf(10);
Regards
Pavel Stehule
2007/9/21, Rhys Stewart [EMAIL
2007/9/25, Rhys Stewart [EMAIL PROTECTED]:
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/24/07, Pavel Stehule [EMAIL PROTECTED] wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
2007/9/25, Scott Marlowe [EMAIL PROTECTED]:
On 9/24/07, Pavel Stehule [EMAIL PROTECTED] wrote:
2007/9/25, Rhys Stewart [EMAIL PROTECTED]:
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/24/07, Pavel Stehule [EMAIL PROTECTED] wrote
UTF8 encoding:
createdb -E UTF8 yourdatabase
Regards
Pavel Stehule
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
2007/10/4, Andreas Strasser [EMAIL PROTECTED]:
Hello,
i'm currently designing an application that will retrieve economic data
(mainly time series)from different sources and distribute it to clients.
It is supposed to manage around 20.000 different series with differing
numbers of
2007/10/4, Jorge Godoy [EMAIL PROTECTED]:
On Thursday 04 October 2007 06:20:19 Pavel Stehule wrote:
I did good experience with 2 variant. PostgreSQL needs 24bytes for
head of every row, so isn't too much efective store one field to one
row. You can simply do transformation between array
Hello
If your stored procedure is writen in C language, then storing it
inside PostgreSQL hasn't any benefit. There is only one difference ..
loading library needs some time, but you can load any library with
statement LOAD.
Regards
Pavel Stehule
2007/10/7, Harpreet Dhaliwal [EMAIL
2007/10/7, Harpreet Dhaliwal [EMAIL PROTECTED]:
My stored procedure is in Perl. Does that really make any difference ?
It depends. If you call external libraries you can be little bit
faster. You have to test it.
Pavel
On 10/7/07, Pavel Stehule [EMAIL PROTECTED] wrote:
Hello
If your
Windows.
Pavel
On 10/7/07, Pavel Stehule [EMAIL PROTECTED] wrote:
2007/10/7, Harpreet Dhaliwal [EMAIL PROTECTED]:
My stored procedure is in Perl. Does that really make any difference ?
It depends. If you call external libraries you can be little bit
faster. You have to test
2007/10/12, Clemens Schwaighofer [EMAIL PROTECTED]:
hi,
thats the first time I am a bit confused by a query not working.
I have this table:
gullevek=# \d test
Table public.test
Column | Type| Modifiers
2007/10/15, Gauthier, Dave [EMAIL PROTECTED]:
Question regarding temp tables
If I (user=joe) attach and run something that uses a temp table, then I
(user=joe again) attach again in another session, will there be 2
distinct temp tables? Or does one user get one temp table per DB?
There
http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE
Thank you, Tom. Since select current_setting('uservars.user_name')
and SHOW uservars.user_name are both PostgreSQL extensions, why not
allow them to be used interchangeably? In short, why isn't the
2007/10/16, Marek Lewczuk [EMAIL PROTECTED]:
Hello,
I'm testing 8.3beta and I think that there is a problem with gist/gin
indexes. The performance of 8.3 is very bad comparing to 8.2. I have a
table with an int[] column indexed using gin (or gist with intarray
module). Table contains about
snip
CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[i] AS the_intersection
FROM generate_series(
array_lower($1,1),
array_upper($1,1)
) AS i
INTERSECT
What's the best way to invoke a process on another server from a PG
trigger or procedure? I was thinking of using pl/java to invoke a web
service on the other box... Can pl/tcl run Expect scripts? That'd be
an option, too. Or I could use XMLBlaster to send a message to the
other box to
2007/10/18, Lothar Behrens [EMAIL PROTECTED]:
Hi,
Several groups I post most of them does not answer.
Did noone see my postings ?
Please at least give me one answer :-)
Thanks, Lothar
I see your post
Pavel
---(end of broadcast)---
TIP 2:
/msg00665.php
# Allow SQL-language functions to reference parameters by parameter name
Currently SQL-language functions can only refer to dollar parameters, e.g. $1
Regards
Pavel Stehule
2007/10/19, Ciprian Dorin Craciun [EMAIL PROTECTED]:
Hello all!
Almost two months ago I have posted
Hello
I am unsure, did you check config values?
Pavel
2007/10/22, Martin Marques [EMAIL PROTECTED]:
I have to PG servers, one ver. 8.1.9 and the other 8.2.4.
I was checking a query out and found that with the exact same DB (same
data in it) and the same query I get different plans, and
2007/10/22, Martin Marques [EMAIL PROTECTED]:
Pavel Stehule wrote:
Hello
I am unsure, did you check config values?
Don't know which ones you are talking about, but all enable_* are set to on.
Anything else?
shared_buffers
work_mem
effective_cache_size
Pavel
--
21:50:04 up 2
2007/10/22, Martin Marques [EMAIL PROTECTED]:
Pavel Stehule wrote:
2007/10/22, Martin Marques [EMAIL PROTECTED]:
Pavel Stehule wrote:
Hello
I am unsure, did you check config values?
Don't know which ones you are talking about, but all enable_* are set to
on.
Anything else
2007/10/23, Martin Marques [EMAIL PROTECTED]:
Martin Marques escribió:
Pavel Stehule wrote:
try
set work_mem to '8MB';
and
explain analyze select ..
These things didn't help. What changed the plan completely was this:
seq_page_cost = 5.0 # measured
2007/10/24, Stefan Schwarzer [EMAIL PROTECTED]:
Hi there,
I read dozens of times the TIP 2: Don't 'kill -9' the postmaster...
Now, what am I supposed to do if I launched a query which takes ages, and
which I want to interrupt?
Thanks for any advice,
Stef
Hello
you have to use more
://www.postgresql.org/docs/8.2/interactive/plperl-global.html and
$_SHARED array.
Regards
Pavel Stehule
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
2007/10/24, Christian Schröder [EMAIL PROTECTED]:
Ow Mun Heng wrote:
look for the query's procpid and then issue a select
pg_cancel_backend('the_id')
Does it do any harm if I kill (either with signal 9 or signal 15) the
single backend process (and not the postmaster)?
shared memory can
, ...
FROM data
WHERE id = ANY(ARRAY(
SELECT (random()*max_id)::int
FROM generate_series(1,20)))
LIMIT 1;
-- max_id is external constant
Pavel Stehule
---(end of broadcast)---
TIP 9: In versions below 8.0
a
series of random tids and join on that.
--
SELECT id, ...
FROM data
WHERE id = ANY(ARRAY(
SELECT (random()*max_id)::int
FROM generate_series(1,20)))
LIMIT 1;
-- max_id is external constant
Pavel Stehule
[];
myarray := '{}';
FOR ___ LOOP
IF (___) THEN
myarray := array_append(myarray, somestuff);
END IF;
END LOOP;
-- Is my array empty now?
IF (myarray isnull) THEN
__
END IF;
IF (array_upper(myarray,1) is null) THEN
END IF;
Regards
Pavel Stehule
Regards,
Alexis Beuraud
On 31/10/2007, T.J. Adami [EMAIL PROTECTED] wrote:
Hi people. I have 2 databases named as follows:
1) company_a
2) company_b
hello
you can access others databases via dblink
look to contrib
Pavel Stehule
---(end of broadcast)---
TIP 5
On 31/10/2007, Anton Andreev [EMAIL PROTECTED] wrote:
Hi,
I have records with date column. Is there a way I can get which day of
week this date is?
Cheers,
Anton
Hello
http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html
EXTRACT or function date_part
Regards
Pavel
to efficiently solve such a problem?
Hello
PostgreSQL 8.3 allows scrollable cursors in plpgsql
Regards
Pavel Stehule
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
good idea.
They can by changed and than you have to recompile your application.,
postgres=# select 'integer'::regtype::int;
int4
--
23
(1 row)
Regards
Pavel Stehule
---(end of broadcast)---
TIP 4: Have you searched our list archives
Hello
PostgreSQL doesn't use index for COUN(*)
http://www.varlena.com/GeneralBits/18.php
http://sql-info.de/en/postgresql/postgres-gotchas.html#1_7
Regards
Pavel Stehule
On 06/11/2007, SHARMILA JOTHIRAJAH [EMAIL PROTECTED] wrote:
Hi
We are in the process of testing for migration of our
. In
that case I have to change my queries to AS t(x integer, y integer, z integer)
Do you know how to declare a generic field list, or some other library
similar to crosstab?
Hello
it isn't possible. PostgreSQL'SQL is strict typed language :(.
Pavel Stehule
one possible implementation
http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29#Usage_of_PL.2FpgSQL_functions_for_designing_own_operators
Pavel
On 08/11/2007, hubert depesz lubaczewski [EMAIL PROTECTED] wrote:
is it just a simple ommission, or am i missing something?
we have interval / float8 ( =
Hello
have you libxml2 on your computer?
Send full configure output, please
Regards
Pavel Stehule
On 09/11/2007, König, Monika [EMAIL PROTECTED] wrote:
Hey,
I try to configure postgresql 8.3beta with libxml by the comand:
LDFLAGS=-lstdc++ ./configure --with-tcl --without-zlib
On 12/11/2007, Gauthier, Dave [EMAIL PROTECTED] wrote:
Is there a function that'll return the position of the last occurance of a
char in a string?
Hello
simply install and use orafce
http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29
Regards
Pavel Stehule
For Example
Hello
use temporary sequence instead.
postgres=#create temp sequence a;
CREATE SEQUENCE
postgres=# select nextval('a'), b FROM (values(1),(2),(10),(20)) b(b);
nextval | b
-+
1 | 1
2 | 2
3 | 10
4 | 20
(4 rows)
Regards
Pavel Stehule
On 13/11/2007, Sarah
Hello
LIMIT has impact on execution plan, so there cannot be variables. Use
SRF function and dynamic statements instead.
Regards
Pavel
On 15/11/2007, Reg Me Please [EMAIL PROTECTED] wrote:
In any case, what'd be the benefit for not allowing variables as LIMIT and
OFFSET argument?
--
Reg me
://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING
But problem can be in
http://groups.google.com/group/pgsql.general/browse_thread/thread/38aa2064fcce53ed/69b7362839c3ab4c
Regards
Pavel Stehule
---(end of broadcast
/index.php?/archives/278-PostgreSQL-full-text-search-testing.html
Summary:
* fulltext, GIS, arrays .. gin, gist indexes (gin is faster, but slow update)
* others .. btree index (I don't know anybody who use hash index)
Regards
Pavel Stehule
---(end of broadcast
hello,
it depends on SQL. plpgsql hasn't own arithmetic unit. You can test it simply:
postgres=# create or replace function a() returns bool as $$ begin
raise notice 'a'; return true; end$$ language plpgsql;
CREATE FUNCTION
postgres=# create or replace function b() returns bool as $$ begin
hello,
you have to initialise array before using. Like:
declare a int[] = '{0,0,0,0,0, .}';
begin
a[10] := 11;
..
reason: older postgresql versions unsuported nulls in array
regards
Pavel
2007/5/31, Anton [EMAIL PROTECTED]:
Hi.
I want to use array for store some values
May I ask some more complex? I want to use ONE multidimensial array -
the id, bytes_in and bytes_out. By another words, I need an
array, each element of which must contain 3 values: ttc_id, bytes_in,
bytes_out.
I think it can be done like this:
It's problem. You have to wait for 8.3 where
initialisation:
FOR p_tmp IN SELECT DISTINCT ON(ttc_id) ttc_id FROM ttc_ids
LOOP
-- get next value for index
i = array_upper(p_ttc_ids, 1) + 1; IF i IS NULL THEN i := 0; END IF;
--RAISE NOTICE '[%]', i;
p_ttc_ids[i] := p_tmp.ttc_id;
p_bytes_in[i] := 0;
p_bytes_out[i] := 0;
END
will see.
For beginner, difference between function and procedure is less than
small in PostgreSQL. There are two families of stored procedures:
Oracle and Microsoft. Look to their documentation.
Regards
Pavel Stehule
---(end of broadcast)---
TIP 5
2007/6/3, Owen Hartnett [EMAIL PROTECTED]:
I know this is in the docs somewhere, and it's probably staring me in
the face, but I haven't been able to find it:
I'm running 8.2.4 through npgsql - how do I log:
1) connections to the database
2) updates, deletes, adds
Hello,
look
statement without two.
Regards
Pavel Stehule
2007/6/4, Ranieri Mazili [EMAIL PROTECTED]:
Hello,
(sorry for my poor english)
It's my first post here, and my doubt is very simple (I guess). I have a
function to populate a table, into WHILE I have the follow piece of code:
--Jump Weekend
IF (SELECT
2007/6/5, David Gardner [EMAIL PROTECTED]:
This post got me thinking, is there a similar procedure for PL/pgSQL functions?
No. You can debug PL/pgSQL function via debug plugin API. Currently
exists only one debugger, which can do it - Enterprisedb debugger.
Regards
Pavel Stehule
Hello
it isn't possible in plpgsql. Try other plperl or plpython
Regards
Pavel Stehule
2007/6/6, Diego Sanchez [EMAIL PROTECTED]:
Hi there.
Is there any way of determining the actual structure of a record variable?
E. g. I've written a small script to do some calculations over some
Hello
I thing so problem is there
AND Cast(CUSTOMERS.ZIP as integer) 2
You cannot cast 'A1234' to integer
Regards
Pavel Stehule
2007/6/11, Pit M. [EMAIL PROTECTED]:
We use PG 8.2.4 with as cursors over libpq and get an error:
ERROR: current transaction is aborted
();
a | b
+
10 | 10
11 | 20
(2 rows)
Regards
Pavel Stehule
2007/6/11, Jeremy Nix [EMAIL PROTECTED]:
Is this possible? I'm attempting to create a function like this and I'm
getting the following error:
ERROR: RETURN NEXT cannot have a parameter in function with OUT
parameters
I didn't try myself, but wrapping the whole into a PL/pgSQL function and
using exceptions might do the work;
It's not good advice. I tested it, and problem is in where clause. I
don't understand problem well, but one possibility is change from cast
to to_number function like:
postgres=#
RETURN NEXT myRecord;
END LOOP;
Thanks,
__
Jeremy Nix
Senior Application Developer
Southwest Financial Services, Ltd.
(513) 621-6699
Pavel Stehule wrote:
Hello
it's possible, but it's probably some different than you expect
CREATE OR REPLACE FUNCTION foo
) 621-6699
Pavel Stehule wrote:
Hello
it's possible, but it's probably some different than you expect
CREATE OR REPLACE FUNCTION foo(OUT a integer, OUT b integer)
RETURNS SETOF RECORD AS $$
BEGIN
a := 10; b := 10;
RETURN NEXT;
a := 11; b := 20;
RETURN NEXT;
RETURN;
END
Yes i know that i can't, but why the transaction fails?
because casting fails. First FETCH was ok, and evaluating of next row
(which need second FETCH) was broken. When any statement in
transaction fail, you have to rollback current transaction.
Pavel
---(end of
Unfortunately this is not possible, because this should happen on the
client. The client calls FETCH for every record available in that cursor
when the user (application) wants to display the data (scrollable
list of records)
So do you think i should wrap each FETCH statement?
We handle
:= ret_foo();
RETURN NEW;
END; $$
LANGUAGE plpgsql;
CREATE TRIGGER footrig BEFORE INSERT ON Foo
FOR EACH ROW EXECUTE PROCEDURE trig();
INSERT INTO Foo VALUES(0,0);
Regards
Pavel Stehule
---(end of broadcast)---
TIP 4: Have you searched our list
i can't do
declare
vals default_vals%rowtype
begin
select get_defaults_vals() into vals;
x1 := default_vals.a1;
try
SELECT INTO vals * FROM get_defaults_vals()
Pavel
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
Hello
maybe:
create function foo(varchar[][]) returns void as $$ begin end$$
language plpgsql;
postgres=# select foo(array[array[1,2], array[2,2]]::varchar[][]);
foo
-
(1 row)
Regards
Pavel Stehule
2007/6/13, Jyoti Seth [EMAIL PROTECTED]:
Hi,
I have to pass a set of values
Hello
please, look on
http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html
Regards
Pavel Stehule
2007/6/13, Karen Springer [EMAIL PROTECTED]:
Hi,
I am struggling to write my first recursive function and think I'm
missing something
1 - 100 of 1333 matches
Mail list logo