Re: [SQL] Generating serial number column
On Thursday 13 Feb 2003 4:21 pm, Mintoo Lall wrote:
> Hi Everybody ,
>
> Is there a way in postgresql by which I can generate serial number column
> for the resultset. I wanted the resulset to look like below
>
>
> sno Name
> ---
> 1JOE
> 2JOHN
> 3MARY
> 4LISA
> 5ANN
> 6BILL
> 7JACK
> 8WILL
> 9GEORGE
> 10 NANCY
> 11 JANE
> ..
> ..
> ..
>
> My query is basically select * name from tblcontact. I wanted to generate
> the column "sno" which basically numbers each row returned. Any help is
> appreciated.
> Regards,
> Tarun
create sequence myseq;
select nextval('myseq'), name from tblcontact;
drop sequence myseq;
There may well be a way to do it without the sequence, bit I can't think of
one.
Gary
>
>
>
> -
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
Ooops! Appologies to all for being sooo stupid!
I thought the order by was applied before the WHERE and if ordering in DESC
order for example < would mean greater than and so on in the where clause
because I assumed < meant it would appear before in the returned order.
Of course the where just selects which values and the order by orders them!
Don't know how I even got confused in the first place. So there's no bug in
POstgres and I just wasted a day being dumb (guess I should get more than 2
hours sleep before tackling this kinda stuff!).
Thanks all,
On Thursday 13 Feb 2003 8:39 pm, Manfred Koizar wrote:
> On Thu, 13 Feb 2003 18:28:50 +0100, Nicholas Allen
>
> <[EMAIL PROTECTED]> wrote:
> >Because the WHERE clause is directly affected by the ORDER BY clause.
>
> No, it's not (at least in your query).
>
> > If you
> >leave out the order by clause then the row count will be completely
> > different and therefore wrong.
>
> I must be missing something. Please give an example.
>
> > The ORDER BY clause is just as important as the WHERE
> >clause when counting rows. It should be possible to get a count for the
> > rows for any query that can be done which can return row data as I
> > understand it.
>
> If you have a set of numbers, say {1, 9, 5, 3, 7}, and want to know
> how many elements of the set are <= 7 (SELECT COUNT(*) FROM s WHERE
> n<=7), you simply look a each element - no matter in what order - and
> increase your counter, if the element satifies your condition. I
> can't see how you get different numbers when you count {1, 3, 5, 7},
> {1, 5, 3, 7} or any other permutation.
>
> >I have tried to find a definition for SQL SELECT command but everywhere I
> > have looked so far makes no mention of this being invalid SQL syntax. Can
> > you let me know where you got this information?
>
> SQL92 says:
> ::=
> [ ]
> [...]
> 3) Let T be the table specified by the .
>
> 4) If ORDER BY is specified, then each in the
> shall identify a column of T.
>
> Servus
> Manfred
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] dblink question please
Many thanks Joe. I think my problem is quite typical one, let me try my best to describe the project. The project is about to build a "twin-database" system, which requires data to be replicated synchronously(only ONE single active master accepts request though), and supports "fail-over"(if one database server is crashed for some reason, then at later time when it is up, it should get synchronized with the current active master.) The two servers' role can be changed, slave -> active master and master -> slave are possible. (I use dblink() functions to connect to the remote host) 8 possible cases identified on what it might happen and how to cope with it: *** Case 1: Both OK, clients connect to S1. Update synchronously. Case 2: S1 OK, S2 failed, clients connect to S1 Update S1 only, mark S2 as unavailable. Case 3: S1 failed, S2 OK, clients connect to S1. Clients timed out, clients then connect to S2 (this has to ben hard-coded in client codes), S2 connects to S1 but failed, upgrade S2 to master, mark S1 as unavailable. Update S1 only Case 4: Both OK, clients connect to S2 S2 checks S1's status in S2's database. S1 available and S2 is able to connect to S1 Return error (S2's not currently the master) Clients then connect to S1. Case 5: (extends case 3) S1 failed, S2 OK and then S1 OK At the beginning, this is handled like case 3 When S1 UP, client should continue to connect to S2. If a new client connects to S1, S1 check its current status, is master, but its status in S2 is slave. Then downgrade S1 to slave and keep S2's database data unchanged. S1 and then should return error (coz it's not the master). Finally clients should connect to S2. Case 6: Case 5 + DB sync. completed. External Program Lock exclusively (don't even allow others to read from it) the status table of S2 (currently the master).Downgrade S2 to slave and upgrade S1 to master in S2.Then upgrade S1 to master and downgrade S2 to slave in S1. Unlock the table. During the process, if clients connect to S1, return error ( coz it's slave now). If clients connect to S2, wait for timeout. (coz its status table has been locked.) o Unlock table, clients connect to S2, return error. (not master now.) Case 7: Case 5 + DB sync processing, S2 failed. NO SOLUTION, S1 cannot must not accept requests (coz not get sync'ed) Case 8: Case 5 + S2 failed, and then S1 OK. NO SOLUTION, S1 doesn't know itself has been failed previously. *** There are several identical tables stored on both servers, we manipulate those tables to simulate transaction(stores the every update delete/insert of requests from clients) The database users mainly access the database by the mean of ADO over ODBC (they are VB programs), a wrapper VB6 library is written so that those VB programs do not need to change their codes much. (Clients are aware of presense of the slave server only when the current active master fails, so that it can try to connect to another.) Now the key problem is how I can just let it be when active master fails to connect to slave(of course it will mark the relative fields of status table, just don't wanna wait too long.) I wanna say thanks again here, for reading through this long and clumsy text by me. Regards, Frankie "Joe Conway" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... > Frankie Lam wrote: > > Now I use only persistent connection inside my PLPGSQL functions, the > > EXCEPTION "ExecMakerTableFunctionResult: Invalid result from function > > returning tuple" has gone. > > Good. > > > It seems to me this is a matter of libPQ (because connect_timeout > > doesn't work in the case), but someone told me this is nothing to do > > with libPQ, and it's possible a matter of KERNEL and transport layer > > of tcp protocol.(I don't really understand these stuff) Is this > > true?(If this is true, then I have to abort my project :-( ) > > > > I'm afraid the person who gave you that answer knows much better than I. > Perhaps there is some kernel tcp parameter you can adjust? Or maybe > dblink just isn't an appropriate solution given your requirements. You > haven't described what exactly you are trying to do, and what exactly > are your requirements, so it's a bit hard to help there. > > Joe > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] PL/PGSQL EDITOR
You can try Crimson Editor, a free powerful editor. http://www.crimsoneditor.com/ Regards Frankie "Eduardo" <[EMAIL PROTECTED]> wrote in message news:b2h2b0$uqd$[EMAIL PROTECTED]... > Do exists a PL/PGSQL editor? > > Thanks. > > > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] PL/PGSQL EDITOR
Eduardo, > Do exists a PL/PGSQL editor? Not that I know of. We've talked about this for PGAccess, but nothing's come of it because nobody has the motivation to code it, yet. PGAccess does have a function editor, but it's rather limited. I've been told that TOra (http://www.globecom.se/tora/) functions as a PL/pgSQL editor if you have the Oracle PL/SQL libraries installed. I haven't tried this, myself. I use KDE's Kate. Good luck! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Passing arrays
Title: Passing arrays
How do you get an array of elements to be passed to a stored proc such that you can use the array in a SELECT statement in the WHERE clause
- e.g. WHERE field IN (array) etc...
CREATE OR REPLACE FUNCTION sp_report_retail_sales(int8[]) RETURNS SETOF sp_report_retail_sales_type AS '
DECLARE
---[Parameters]---
prod_id ALIAS FOR $1;
---[Variables]
retset record;
BEGIN
FOR retset IN
SELECT tbl_prdcrd.fld_prdcrd_id,
count (tbl_tranitem.fld_tranitem_productid) as num_sales,
sum (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity) as base_total,
sum (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity * tbl_tranitem.fld_tranitem_gst) as gst_total
FROM tbl_prdcrd INNER JOIN tbl_tranitem ON tbl_prdcrd.fld_prdcrd_id = tbl_tranitem.fld_tranitem_productid
INNER JOIN tbl_tran ON tbl_tranitem.fld_tranitem_transactionid = tbl_tran.fld_tran_id
WHERE tbl_prdcrd.fld_prdcrd_id IN (prod_id)
GROUP BY tbl_prdcrd.fld_prdcrd_id,
tbl_prdcrd.fld_prdcrd_type
LOOP
RETURN NEXT retset;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
We were calling the procedure as follows
select * from sp_report_retail_sales1 ('{1,3}');
We keep getting issues with casting.
WARNING: Error occurred while executing PL/pgSQL function sp_report_retail_sale
s_01
WARNING: line 8 at for over select rows
ERROR: Unable to identify an operator '=' for types 'bigint' and 'bigint[]'
You will have to retype this query using an explicit cast
Trying the parameter as text worked, but gave 0 rows in result set.
,
Mike Weaver
Software Developer
5, 42 Ladner Street
O'Connor, WA, 6163
All correspondence:
PO Box Y3502
East St Georges Terrace
Perth WA 6832
P: (+618) 9331 2700
F: (+618) 9331 3733
M: 0403 385 181
W: http://www.corpusglobe.com/
E: [EMAIL PROTECTED]
This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You.
Re: [SQL] Passing arrays
Micheal, > How do you get an array of elements to be passed to a stored proc such that > you can use the array in a SELECT statement in the WHERE clause > - e.g. WHERE field IN (array) etc... There's no easy way, right now. You'll have to: 1) loop through the array and pass its elements to a comma-delimited TEXT variable. 2) Build up your query as a dynamic string. 3) Do your query loop as a FOR record IN EXECUTE dynamic_query_string -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] rownum
sorry about this - braindead and cannot find in doc. what's pg's rownum pseudo-column or function name that returns the record number of a set? __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] rownum
Chester, > sorry about this - braindead and cannot find in doc. what's pg's > rownum pseudo-column or function name that returns the record number of > a set? There isn't one, unless there's something in /contrib that you can build. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Passing arrays
On Fri, 14 Feb 2003, Michael Weaver wrote: > How do you get an array of elements to be passed to a stored proc such that > you can use the array in a SELECT statement in the WHERE clause > - e.g. WHERE field IN (array) etc... A straight WHERE field IN (array) doesn't work. That's attempting to compare the field directly to the array as a whole (which is why you get the particular error you do. You might want to look at contrib/array for some loadable functions/operators in working with arrays. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] rownum
Josh Berkus <[EMAIL PROTECTED]> writes:
>> sorry about this - braindead and cannot find in doc. what's pg's
>> rownum pseudo-column or function name that returns the record number of
>> a set?
> There isn't one, unless there's something in /contrib that you can build.
Right now the only way I've heard of is to use a sequence, for example
create temp sequence rownum;
select nextval('rownum'), ... from ...;
drop sequence rownum;
This is a hack, and it will fail if the SELECT involves any sort
specification (not only ORDER BY, but DISTINCT) because the nextval()s
will be computed before sorting. You can get around that with
select nextval('rownum'), * from (select ... order by ...) sub;
The overhead of using a sequence for this is pretty annoying. It would
be a simple matter to write a C function that emits sequential values
without any database access (see pg_stat_get_backend_idset() for some
inspiration). But you'd still need the subselect to avoid getting
re-sorted. AFAICS any rownum() function that doesn't behave like that
is a flat violation of the SQL standard...
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Debugging postmaster to fix possible bug in Postgres? Followup
SQL92 says: ::= [ ] [...] 3) Let T be the table specified by the . 4) If ORDER BY is specified, then each in the shall identify a column of T. Then it looks like postgres behaviour is still not compliant, if I read it correctly, because select x from mytable order by y; should be invalid according to this, but works just fine in postres. Dima. P.S. I think, this is a great feature actually (missed it a lot in informix), so, I hope, you guys won't start discussing how to fix it :-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Extending Datatype
Where can I find a more detailed doc on how to write module for extended datatype? There isn't much about it on techdoc.postgresql.org. (I need to write one for UNIQUEIDENTIFIER.) Thanks! Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] postmaster -i & establishes three connections why?
Hi, I've just upgraded my cygwin to 1.3.20-1 and postgresql to 7.3.2 And I found that when run postmaster -i & There are three connections being established and why is that? Administrator@DemoServer ~ $ LOG: database system was shut down at 2003-02-13 17:33:26 EST LOG: checkpoint record is at 0/841330 LOG: redo record is at 0/841330; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 484; next oid: 16976 LOG: database system is ready $ ps -l PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 2292 12292 2292 con 500 17:43:20 /usr/bin/bash 232022922292 2324 con 500 17:43:22 /usr/local/bin/ipc-daemo n 209222922292 2404 con 500 17:43:22 /c/progra~1/apache~1/apa che/cgi-bin/printspool 231222922292 2452 con 500 17:43:24 /usr/bin/postgres 204823122292 2048 con 500 17:43:28 /usr/bin/postgres 188420482292 1884 con 500 17:43:28 /usr/bin/postgres 250422922504 1960 con 500 17:43:36 /usr/bin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Debugging postmaster to fix possible bug in Postgres? Followup
On Thu, 13 Feb 2003 17:50:22 -0500, Dmitry Tkach <[EMAIL PROTECTED]> wrote: >Then it looks like postgres behaviour is still not compliant, if I read it correctly, >because > >select x from mytable order by y; > >should be invalid according to this, but works just fine in postres. Yes, this is a Postgres extension to the standard. Your query is handled like SELECT x, y FROM mytable ORDER BY y with y being eliminated after the sort step. This also explains why the OP got the error message ERROR: Attribute t.y must be GROUPed or used in an aggregate function because the implicitely rewritten form would look like SELECT COUNT(*), y FROM t WHERE ... ORDER BY y >P.S. I think, this is a great feature actually (missed it a lot in informix), > so, I hope, you guys won't start discussing how to fix it :-) AFAICT there's no need to worry. Everyone agrees that this is a good feature and it does not break standard SQL queries. Servus Manfred ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Extending Datatype
Try the SERIAL data type. It uses sequences and DEFAULT. Read the FAQ. --- Wei Weng wrote: > Where can I find a more detailed doc on how to write module for extended > datatype? There isn't much about it on techdoc.postgresql.org. > > (I need to write one for UNIQUEIDENTIFIER.) > > Thanks! > > > Wei > > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Debugging postmaster to fix possible bug in Postgres? Followup
Dmitry Tkach <[EMAIL PROTECTED]> writes: > Then it looks like postgres behaviour is still not compliant, if I read it >correctly, because > select x from mytable order by y; > should be invalid according to this, but works just fine in postres. Yup, it's an extension --- as indeed is pointed out at the bottom of our SELECT reference page. But it's a well-defined extension, because every row of the result does have a clearly associated value of y. Once you throw in GROUP BY or aggregates, you can't order by values that aren't constrained by the grouping. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL Functions vs PL/PgSQL
Roberto Mello <[EMAIL PROTECTED]> writes: > On Thu, Feb 13, 2003 at 09:02:05AM -0800, Josh Berkus wrote: >> Unless you know something I don't, I do not believe that PL/pgSQL stores >> execution plans for functions. > AFAIK execution plans for PL/pgSQL functions were cached once per backend. > No? Yes. This is explained in the docs ... http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html#PLPGSQL-OVERVIEW regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] table Pivot
How do you do a table Pivot in PostgreSQL? tia, .V = ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL Functions vs PL/PgSQL
Johnny, > Does postgres store execution plan's for user-defined SQL functions as the > PL/pgSQL interpreter does. And if so - will this only be prepared after an > initial call to the function? Can't seem to find this information in the > docs!? Thanks, Unless you know something I don't, I do not believe that PL/pgSQL stores execution plans for functions. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
Because the WHERE clause is directly affected by the ORDER BY clause. If you leave out the order by clause then the row count will be completely different and therefore wrong. The ORDER BY clause is just as important as the WHERE clause when counting rows. It should be possible to get a count for the rows for any query that can be done which can return row data as I understand it. I have tried to find a definition for SQL SELECT command but everywhere I have looked so far makes no mention of this being invalid SQL syntax. Can you let me know where you got this information? Thanks, On Thursday 13 Feb 2003 4:17 pm, Tom Lane wrote: > Nicholas Allen <[EMAIL PROTECTED]> writes: > > I then commented out the line just to see if this would fix the problem. > > Then I rebuilt it started the server up and connected. I performed the > > count query as I described bfore and it worked perfectly! It did exactly > > what I wanted! Now obviously the code was in there for some reason but it > > seems that it is not necessary to check it in this case. There must be a > > bug here surely. MySQL also allows it so I don't think it is invalid SQL > > on my part. And the fact it works perfectly if I disable this check is > > very promising. > > There is no bug here, or wasn't until you broke it. The given query is > illegal according to the SQL standard (MySQL is a fairly unreliable > guide to standard behavior :-(). It seems quite useless anyway: > "SELECT count(*)" will return exactly one row, so what's the meaning of > putting an ORDER BY clause on it? > > > Now if I execute this (note only difference is change from * to > > count(*)): > > > > select count(*) FROM vu_tbl_user_all_s WHERE s_surname < 'Asurname' or > > (s_surname = 'Asurname' and s_alias <= 'CISX' and s_loginid <= > > 'Loginid8') ORDER BY s_surname, s_loginid; > > > > I get this: > > > > ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in > > an aggregate function > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
