Hi
I've got a bit of a tricky (or me!) problem. The example below is
completely ficticious but
describes my real problem in a way which might be easier to understand.
Imagine your table contains
CREATE TABLE passenger_queue (
id serial NOT NULL,
name character varying(40) NOT NULL,
On Fri, 2006-04-28 at 12:56 +0200, A. Kretschmer wrote:
> am 28.04.2006, um 16:14:10 +0530 mailte Penchalaiah P. folgendes:
> > 4) The following is the function that retrieves the records from pss :
> >
> > CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2
> > LANGUAGE 'plpgsq
CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2
LANGUAGE 'plpgsql'
AS' DECLARE
rowdata pss%rowtype;
BEGIN for i in 1..3 loop
select * into rowdata from pss ;
return next rowdata ;
end loop;
return;
end';
The query should be outside the loop, otherwise you are re
am 28.04.2006, um 16:14:10 +0530 mailte Penchalaiah P. folgendes:
> 4) The following is the function that retrieves the records from pss :
>
> CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2
> LANGUAGE 'plpgsql'
>
> AS' DECLARE
> rowdata pss%rowtype;
> BEGIN for i in 1..
Hi
Please spare some time to
provide a solution for the described problem :
I am using set returning functions to return all the records
from a table named pss ,
But what I am getting is the first record is returned as
many times , the number of records present in the rank_mas
Markus Schaber <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> The fact that the other form is even allowed is more of a holdover from
>> PostQUEL than something we have consciously decided is a good idea.
>> (IMHO it's actually a fairly *bad* idea, because it does not work nicely
>> when there's
Hi, Tom,
Tom Lane wrote:
> The behavior of the subquery expression is dictated by the SQL spec:
>
> 1) If the cardinality of a or a is
> greater than 1, then an exception condition is raised: cardinal-
> ity violation.
That's interesting to know, and it seems
Markus Schaber <[EMAIL PROTECTED]> writes:
> postgres=# select (select generate_series(1,2)),'othercol';
> ERROR: more than one row returned by a subquery used as an expression
> So it seems that set-returning functions "blow up" the resultset by
> duplicating rows - so why is this not allowed fo
Hello,
Today, I stumbled across the following:
postgres=# select generate_series(1,2),'othercol';
generate_series | ?column?
-+--
1 | othercol
2 | othercol
(2 rows)
postgres=# select (select generate_series(1,2)),'othercol';
ERROR: more tha
Look contrib/intarray
On Tue, 14 Jun 2005, Sophie Yang wrote:
Say I have a table tbl1 with two columns:
tbl1(a integer, b integer, c integer)
I want to select the rows in which a and b are members
of a list of integer pairs. The SQL in my mind is
something like:
select * from tbl1 where (a,
Say I have a table tbl1 with two columns:
tbl1(a integer, b integer, c integer)
I want to select the rows in which a and b are members
of a list of integer pairs. The SQL in my mind is
something like:
select * from tbl1 where (a, b) in ((1, 20), (2,
30), (3, 50));
I know the SQL above does n
Alvaro Nunes Melo <[EMAIL PROTECTED]> writes:
> I noticed what i think is a weird behaviour with SRFs. When I use them
> in a SELECT clause, and the SRF returns no rows, the query returns no
> rows too, when I think it should return an a null column.
Why would you think that? The behavior is exac
Hi,
I noticed what i think is a weird behaviour with SRFs. When I use them
in a SELECT clause, and the SRF returns no rows, the query returns no
rows too, when I think it should return an a null column. I'm posting an
example below:
test=> CREATE TABLE foo (cod SERIAL, bar TEXT);
NOTICE: CREAT
sad wrote:
On Wednesday 01 December 2004 18:42, Tom Lane wrote:
Christoph Haller <[EMAIL PROTECTED]> writes:
Please, could someone point me to the right list
or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq.
libpq does not have any support for that.
Does this mean libpq calls always unc
On Wednesday 01 December 2004 18:42, Tom Lane wrote:
> Christoph Haller <[EMAIL PROTECTED]> writes:
> > Please, could someone point me to the right list
> > or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq.
>
> libpq does not have any support for that.
Does this mean libpq calls always
Christoph Haller <[EMAIL PROTECTED]> writes:
> Please, could someone point me to the right list
> or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq.
libpq does not have any support for that.
regards, tom lane
---(end of broadcast)--
I cannot find the autocommit-off-switch within the libpq
interface.
TIA
Regards, Christoph
BTW
In ./postgresql-7.4.5/doc/html/ecpg-commands.html
there is still a line saying
This mode can be explicitly turned off using EXEC SQL SET AUTOCOMMIT TO
OFF.
---(end of broadcast
Thank you, Tom.
Jie
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 12, 2004 4:06 PM
To: Jie Liang
Cc: Christian Kratzer; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: Re: [ADMIN] [SQL] \set
"Jie Liang" <[EMAIL PROTECTED]> writes:
&g
"Jie Liang" <[EMAIL PROTECTED]> writes:
> You will see AAA associate with 'whatever', it's an internal variable,
> but how could I use it in my SQL query?
regression=# \set AAA 'whatever'
regression=# select :AAA;
ERROR: column "whatever" does not exist
regression=# \set AAA '\'whatever\''
regres
able,
but how could I use it in my SQL query?
Thanks anyway.
Jie Liang
-Original Message-
From: Christian Kratzer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 12, 2004 3:13 AM
To: Jie Liang
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [SQL] \set
Hi,
On Tue, 11 May 2004, J
Hi,
How to use an internal variable?
Original question was how to set a variable in postgresql?
If I want to set a variable like start_date='2004-05-10';
How could I use it in my SQL statement?
E.g.
Db> set start_date '2004-05-10'
Db> select start_date as 'start date';
It's not executable!
Than
hi,
does any version of postgresql support the 'SET' idiom in an insert
statement? Need this to generate an insert statement from a python dictionary
--
regards
kg
--
http://www.ootygolfclub.org
---(end of broadcast)---
TIP 6: Have you searched ou
ow <[EMAIL PROTECTED]> writes:
> --- Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
>> dynacom=# \c bab
>> You are now connected to database bab.
> I'm actually looking for a way to set database from a *script*, not psql
> console, if this is possible (similar to "SET search_path TO whatever;").
I
--- Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
[...]
> dynacom=# \c bab
> You are now connected to database bab.
> bab=#
I'm actually looking for a way to set database from a *script*, not psql
console, if this is possible (similar to "SET search_path TO whatever;").
Any ideas? Thanks
> Suppose, one server/postmaster is running several databases. Is there
> something like "SET database TO whatever" (and I mean *database* not
> schema). I need this to ensure that a script runs only against db for
> which it was desinged.
You can use PG* environment variables if you wish to write
On Mon, 15 Sep 2003, ow wrote:
> Hi,
>
> Suppose, one server/postmaster is running several databases. Is there something
> like "SET database TO whatever" (and I mean *database* not schema). I need this
> to ensure that a script runs only against db for which it was desinged.
[EMAIL PROTECTED] ~
Hi,
Suppose, one server/postmaster is running several databases. Is there something
like "SET database TO whatever" (and I mean *database* not schema). I need this
to ensure that a script runs only against db for which it was desinged.
Thanks
__
Do you Yahoo
--- Rodger Donaldson <[EMAIL PROTECTED]> wrote:
> On Mon, Jun 16, 2003 at 05:02:34PM -0500, Bu, Ning
> wrote:
>
> > I have a field set up as money and I try to
> calculate some number
> > and put in this field, but the number will be 6
> demical point and I
> > want to cut it to only 2-4 decimal p
On Mon, Jun 16, 2003 at 05:02:34PM -0500, Bu, Ning wrote:
> I have a field set up as money and I try to calculate some number
> and put in this field, but the number will be 6 demical point and I
> want to cut it to only 2-4 decimal point, how can I do it? Thanks
round(); eg
select round(foo, 2
I have a field
set up as money and I try to calculate some number and put in this field, but
the number will be 6 demical point and I want to cut it to only 2-4 decimal
point, how can I do it? Thanks
<>
Christoph Haller wrote:
Sorry to bother again with my question. Is it too stupid or
trivial to this list? Should I send it to NOVICE?
Regards, Christoph
There are no stupid questions, there are only stupid answers ;-)
Anyway - try to not create more threads for the same subject if possible.
Re
>
> > >
> > > T1 (within psql):
> > > BEGIN; DELETE FROM ;
> > > DELETE n
> > >
> > > T2 (within psql):
> > > BEGIN; DELETE FROM ;
> > >
> > >
> ...
> >
> >I don't think there is a deadlock in the example
> > given above. If I'm not mistaken a deadlock occurs if
> > both transactions are wai
On Wed, 13 Nov 2002, Archibald Zimonyi wrote:
>
> Hi everyone,
>
> I have a problem with adding a column to an existing table. I want to add
> a column named modified which is of datatype TIMESTAMP and has a DEFAULT
> CURRENT_TIMESTAMP as it's initial value. I cannot do this with ALTER
> TABLE, it
Hi everyone,
I have a problem with adding a column to an existing table. I want to add
a column named modified which is of datatype TIMESTAMP and has a DEFAULT
CURRENT_TIMESTAMP as it's initial value. I cannot do this with ALTER
TABLE, it tells me to user ALTER TABLE SET DEFAULT instead but I can
=?iso-8859-1?Q?Domoszlai_L=E1szl=F3?= <[EMAIL PROTECTED]> writes:
> So, do you think a DIFFERENCE or EXCEPT SYMMETRIC clause will be reasonable
> feauture for postgresql?
I doubt it; haven't heard a request for it before, and AFAICS there's no
such thing in the SQL spec...
Tom Lane wrote:
> > I would like to make symmetrical(set) difference in a query.
> > But the simpliest way I could find is
>
> > select id from a
> > except
> > select id from b
> > union
> > select id from b
> > except
> > select id from a
>
> > Is there any better solution for this problem?
=?iso-8859-1?Q?Domoszlai_L=E1szl=F3?= <[EMAIL PROTECTED]> writes:
> I would like to make symmetrical(set) difference in a query.
> But the simpliest way I could find is
> select id from a
> except
> select id from b
> union
> select id from b
> except
> select id from a
> Is there any better
Hello,
I would like to make symmetrical(set) difference in a query.
But the simpliest way I could find is
select id from a
except
select id from b
union
select id from b
except
select id from a
Is there any better solution for this problem?
Thanks
Laca
---(end of
Hi folks!
There are a buch of varibles like DateStyle. I am interested to set
them
all in script shell.
I've tried in postgresql.conf, but I could not.
I've tried in shell environment, but it did not work.
So, how can I do that?
..
I need to use the \set command in regular sql (I am running postgres queries
through php and it does not seem to support the \ commands in general). How
can I set a variable with regular sql?
I want to be able to do the following:
\set testvar '1,2,3,4,5'
\set testvar '6,7,8,':testvar
Which is
Hello Alessandro,
The manual I have says the following about Date/Time Styles:
- 4 styles (ISO-8601, SQL, Postgres, German)
The one which resembles your layout is German.
I looked it up in Bruce Momjians book.
Best regards,
Roelof
Alessandro Rossi schreef:
>
> I have the defaul installation o
Alessandro Rossi wrote:
> I have the defaul installation of postgres 7.0.3 and on another machine
> 7.1.2 on redhat 7.1
>
> I cannont get the date in correct form:
.
> Is this a bug ?
>
> I think i should get dd-mm-yyy date format and not -mm-dd
>
> Is postgres using ISO date format as
I have the defaul installation of postgres 7.0.3 and on another machine
7.1.2 on redhat 7.1
I cannont get the date in correct form:
dbme=# select data_ar from equipment limit 5;
data_ar
2001-11-05
2001-05-17
2001-05-28
2001-05-28
2001-05-22
(5 rows)
then:
dbme=# set dat
Hallo,
i have postgres 7.1 in Radhat 6.5
the Datestype default is ISO format
where is change default Datestyle to Postgres format ??
i used Set Datestyle to postgres in plsql
test#>select now()
now
2001-05-21 09:55:36+07
test#>set datestyle to postgres;
S
I have the command below like it is described in the documentation
(http://www.archonet.com/pgdocs/force-index.html) but it doens't work.
SET SEQSCAN TO OFF;
ERROR: 'seqscan' is not a valid option name
Does anybody know why?
Hans
---(end of broadcast)
> I have the command below like it is described in the documentation
> (http://www.archonet.com/pgdocs/force-index.html) but it doens't work.
>
> SET SEQSCAN TO OFF;
> ERROR: 'seqscan' is not a valid option name
>
>
> Does anybody know why?
>
Try:
test=> set enable_seqscan to off;
Hans-Jürgen Schönig wrote:
>
> I have the command below like it is described in the documentation
> (http://www.archonet.com/pgdocs/force-index.html) but it doens't work.
>
> SET SEQSCAN TO OFF;
> ERROR: 'seqscan' is not a valid option name
>
> Does anybody know why?
>
because the documentat
Hans-Jürgen Schönig writes:
> I have the command below like it is described in the documentation
> (http://www.archonet.com/pgdocs/force-index.html) but it doens't work.
>
> SET SEQSCAN TO OFF;
> ERROR: 'seqscan' is not a valid option name
>
> Does anybody know why?
Because 'seqscan' is not a v
set digest
49 matches
Mail list logo
