> Try this
>
> CREATE SEQUENCE just_a_seq;
> Select nextval('just_a_seq') as row_no, * from pg_tables ;
> drop SEQUENCE just_a_seq;
Wow! Great idea! :-)
In fact I need row numbering in browsing and printing invoices. They
should be read on various platforms (Windows clients, generated HTML a
> Here is a method which is fairly cumbersome, but will do what you want.
> (Whether what you want is useful, is another matter. The row numbers
> have no meaning except to delineate which row is printed after which; they
> bear no relation to their order in the table.)
Thanks, Oliver! Are you su
> Use the "serial" column type.
Unfortunately it's not what I expect. Assume that I have an arbitrary
"SELECT expr1 as column1, expr2 as column2, ..." which gives
column1 | column2 | ...
+-+- ...
..data..
I would like to get the same res
Maybe a trivial question, maybe it's foreign from SQL, I'dont know...
How to add a column which stands for the row number in each row of the
result? E.g.:
row_no | column1 | column2 | ...
---+-+-+ ...
1 | datum11 | datum12 | ...
2 | datum21 | datum22 | ...
... |
Unfortunately you cannot use parameters as you like. The FROM clause
cannot contain a parameter. It must be constant.
Zoltan
--
Kov\'acs, Zolt\'an
[EMAIL PROTECTED]
http://www.math.u-szeged.hu/~kovzol
On Sat, 27 Jan 2001, Peter Eisentraut wrote:
> Kovacs Zoltan writes:
>
> > There seems to be an optimizer problem in 7.1beta3. The query you can see
> > below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is
> > that an 'index scan' has bee
There seems to be an optimizer problem in 7.1beta3. The query you can see
below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is
that an 'index scan' has been changed to a 'seq scan'. Details:
CREATE F
On Thu, 18 Jan 2001, Stephan Szabo wrote:
> Well, you should have gotten an error message from the statement that was
> in error in any case, but maybe a message on the commit/end that says
> that the transaction was aborted due to errors would be nice.
Or both.
Zoltan
I realized that an error will abort a transaction all the time.
Unfortunately I usually send millions of rows of INSERTs in a transaction
and if there is some error in the middle of the code I got the message
if I try to INSERT a new row:
NOTICE: current transaction is aborted, queries ignored u
I experienced terrible speed decrease with some recursive PLPGSQL
functions in 7.1beta3. Has anybody got similar behaviour? Details
soon...
Zoltan
> Saluton,
>
> does anyone know whether there is any support at all for arrays
> in PL/pgSQL?
A second comment: as far as I know, no support for built-in array
variables at all. I usually create temporary tables for doing jobs I need
arrays.
Zoltan
On Tue, 2 Jan 2001, Albert REINER wrote:
> Saluton,
>
> does anyone know whether there is any support at all for arrays
> in PL/pgSQL?
Yes, but it is not suggested for use. See
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz/Linux/PostgreSQL/strukturak/examples.tar.gz,
function csoporttag_e.
> CREATE FUNCTION autono (text,text) RETURNS text AS '
> DECLARE
> a1 ALIAS FOR $1;
> a2 ALIAS FOR $1;
> ret_val text;
> BEGIN
> select tna into ret_val from a1 where pamt_no=a2;
>
> RETURN ret_val;
> END;' LANGUAGE 'plpgsql'
>
>
> error run " select tna into ret_val
> A problem (IMHO it's not problem) with 'now()::abstime' is in 7.1 too.
> But why you not use directly now()?
My aim is to get this format for the time '5 minutes ago'. So I need
something like
select to_char(now()-'5 minutes','YYMMDDHH24MI')
but this doesn't work due to the missing type conv
Hi, this query gives different strange results:
select to_char(now()::abstime,'YYMMDDHH24MI');
I get e.g. a "backend closed the channel unexpectedly..." error with
successful or failed resetting attempt (indeterministic)
or
ERROR: to_char/to_number(): not unique decimal poit
or
NOTICE: Por
> Is it possible to return an array from a plpgsql function??
AICR, it is impossible. In addition, it's not suggested using arrays at
all: the array based logic is foreign from SQL solutions. In fact
PostgreSQL doesn't have a good array support. My friend who built our
logic data model, said that
> Hi, there,
>
> Is there any way to handle exception ( such as cannot insert duplicate key on
> a unique index) in
> plpgsql function?
>
> I don't want it abort whole transaction instead I want to do something else if
> it happened,
> but I don't want to use a select stmt first to waste the tim
On Mon, 20 Nov 2000, Bruno Boettcher wrote:
> On Mon, Nov 20, 2000 at 06:06:52PM +0100, Kovacs Zoltan Sandor wrote:
> > > FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP
> > My opinion is the problem that you cannot give a column name as a
> > par
> FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP
My opinion is the problem that you cannot give a column name as a
parameter. But I'm not sure. This never worked for me. The thing here
happens that you get all rows if and only if $1=$2 (as strings) and if
they are not equal
> Probably a very simple question, but how do you define a function that
> returns the sucess of an insert or update in a function i.e.
>
> CREATE FUNCTION foo ( varchar, int8 ) RETURNS bool
> AS 'UPDATE table WHERE something'
> LANGUAGE 'sql';
With SQL functions you can't (as far as I kn
On Mon, 23 Oct 2000, najm Hashmi wrote:
> Hi, I would to know if there is any articles or books that talk about
> pl/pgsql performance especially versus C. Thanking you in advance for
> your help.
I can only say what the documentation says about it in the "Overview"
section (please read it if
Hi Najm,
have you tried the test examples shipped in the tarball (src/pl/plpgsql/test)?
In addition, you can find *lots* of examples (mostly with Hungarian comments)
on ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz/Linux/PostgreSQL/strukturak/*
PLPGSQL is one of the best things in PostgreSQ
> Is it possible to return recordsets using pl/pgsql?
> If so, how do I declare the return type in the declaration?
AFAIK you can't. I suggest you using temporary tables instead.
Regards, Zoltan
I would like to do something like this:
create function x(varchar) returns setof int4 as
'select * from $1;' language 'sql';
Unfortunately, the parser drops me back: I cannot use any parameters after
the keyword "from". How to solve this? A solution in PL/PgSQL would be
also nice.
Thanks in adv
I realized the following facts using SQL language functions:
There is a function "function_y(...)" which returns int4; a table z and
two functions:
CREATE FUNCTION function_x1() RETURNS int4 AS '
select function_y(any_of_fields_of_table_z) from z;
' LANGUAGE 'SQL';
This calls function_y(...) on
25 matches
Mail list logo
