[SQL] Debugging ?
How do I do some tracing to debug a Function... ? I've read that to raise warnings / Exception / ... is the only mean (beside not having any faults) & then look in Logs. Is it so ? Tx, Thomas, -- Thu Jan 4 20:19:03 CET 2001 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = Jealousy is all the fun you think they have. = End of Quote ===
[SQL] Casting
Hi, In pgsql I'm removing charaters from a String which should be numbers. I then want to make calculations on these numbers (calculate the ISBN number). Do I have to cast the char into int's before I can do the calulations. Also I looked in the User manual but could not find the modulo function where is it ? tx, Thomas, -- Thu Jan 4 20:19:03 CET 2001 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = Jealousy is all the fun you think they have. = End of Quote ===
Re: [SQL] psql -f option
Graham Vickrage writes: > The documentation says -f enables some nice features such as error messages > with line numbers. It seems to me that this is half true i.e. it shows me > error messages, its doesn't however give me the associated line number in > the script. Works here: $ psql -f /etc/sysctl.conf psql:/etc/sysctl.conf:8: ERROR: parser: parse error at or near "#" It looks to me like you are using an older psql, perhaps from a previous installation. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] psql -f option
"Graham Vickrage" wrote: >I am trying to use the psql -f option to load a script into the >DB ( v7.0 ) from the linux command line. > >The documentation says -f enables some nice features such as error messages >with line numbers. It seems to me that this is half true i.e. it shows me >error messages, its doesn't however give me the associated line number in >the script. This would be a very useful feature for me as my scripts can be >very long. > >Is there a configuration option i am missing? Use -e as well, to have the SQL queries echoed; then you can see where the errors are arising. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But because of his great love for us, God, who is rich in mercy, made us alive with Christ even when we were dead in transgressions-it is by grace you have been saved."Ephesians 2:4,5
[SQL] Re: [PHP] GUI interface
Julio, > The best I've seen is a web interface -- phpPgAdmin. I think it's from > phpwizards or it's a project on sourceforge -- search google for it and > you'll have yourself a link. It allows gui control for most common tasks, > and you can submit raw sql from it too for more exotic tasks. Also, pgAccess, a tcl/tk GUI, is excellent although incomplete. I end up using a combination of pgAccess, KpgSQL, and command line access. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] sql/database admin courses
Mr. Vanags,
Please be aware that you e-mailed a PostgreSQL developer mailing list.
If you were looking for courses on Microsoft SQL Server, this is an
inappropriate forum for such requests. You might, however, try
www.infotech.com for Microsoft training.
IF you need formal instruction in PostgreSQL, you are out of luck at
this time. However, any number of institutions may offer it soon, and
O'Reilly has a new book pending that covers many SQL databases ("SQL in
a Nutshell").
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] psql -f option
I am trying to use the psql -f option to load a script into the DB ( v7.0 ) from the linux command line. The documentation says -f enables some nice features such as error messages with line numbers. It seems to me that this is half true i.e. it shows me error messages, its doesn't however give me the associated line number in the script. This would be a very useful feature for me as my scripts can be very long. Is there a configuration option i am missing? Thanx in advance Graham winmail.dat
Re: [SQL] Support for arrays in PL/pgSQL
> 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
Re: Sv: [SQL] how to build this query ??? Please help !!!
On Thu, 4 Jan 2001, Jens Hartwig wrote: > This would be a self-join of one table like: > > select ord.a_nr, > c1.count(*), > c2.count(*), > ... > from orders ord, > cylinders c1, > cylinders c2, > ... > where c1.z_a_nr = ord.a_nr > and c2.z_status = 'zdr' > and ... > > This in fact is not possible in PostgreSQL (it seems that the table > alias "c1" cannot be prefixed to the aggregate-function "count(*)") That's true, and you can't say count(c1.*) either, but you *can* say count(c1.z_status)... Continuing from your example using tables x and y: # select count(x1.a) as ones, count(x2.a) as twos from x x1, x x2 where x1.a = 1 and x2.a = 2; ones | twos --+-- 1 |1 (1 row) But it doesn't do what you want: # insert into x(a) values(2); INSERT 313887 1 # select count(x1.a) as ones, count(x2.a) as twos from x x1, x x2 where x1.a = 1 and x2.a = 2; ones | twos --+-- 2 |2 (1 row) An inspection of the cartesian product (select x1.a, x2.a from x x1, x x2) will make it clear why it doesn't work. I can't think of any way to get this: ones | twos --+-- 1 |2 without subqueries like so: # select (select count(*) from x where a = 1) as ones, (select count(*) from x where a = 2) as twos; But, to answer your question, "Does this at all correlate with the philosophy of a relational database?" ... My answer is yes! After all, isn't it just the same as "select a, count(a) from x group by a" turned sideways? If you can think of how to do this "the hard way" (i.e. without subselects or temp tables etc.) please share. -- Tod McQuillin
Re: [SQL] Support for arrays in PL/pgSQL
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. HTH, Zoltan
Re: Sv: [SQL] how to build this query ??? Please help !!!
Hello Tom, > [...] > > SELECT a, (SELECT b) > > FROM xyz; > [...] > I think it's OK (we're assuming that a and b are columns of xyz, right?) > [...] immediately after having sent my message I realized my fault: a and b are not of the same table! Correctly, the statement had to be something like: SELECT a, (SELECT b FROM z WHERE b = a) FROM x; > [...] > This is not really different from >SELECT x FROM xyz WHERE y IN >(SELECT a FROM abc WHERE b = xyz.z); > [...] Now it is :-) In a subquery, the inner query is only used for things like comparison (as it is in your example). In my example the result shows me two columns (in one record!!) which belong to different tables. Mmmmh ... I tested the following: create table x (a numeric); create table y (b numeric); insert into x values (1); insert into x values (2); insert into y values (1); insert into y values (2); select a, (select b from y) from x; => ERROR: More than one tuple returned by a subselect used as an expression. This is ok, anything else would have shocked me. select a, (select b from y where b = a) from x; a | ?column? --+-- 1.00 | 1.00 2.00 | 2.00 This result made me understanding that this special case of "subqueries" is possibly nothing more than a special form of joins between tables: select a, b from x, y where x.a = y.b; brings the same result. Now, back to the first example (of Nikolaj): SELECT a_nr, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zdr') AS #zdr, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcu') AS #zcu, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcr') AS #zcr, product, state FROM orders; This would be a self-join of one table like: select ord.a_nr, c1.count(*), c2.count(*), ... from orders ord, cylinders c1, cylinders c2, ... where c1.z_a_nr = ord.a_nr and c2.z_status = 'zdr' and ... This in fact is not possible in PostgreSQL (it seems that the table alias "c1" cannot be prefixed to the aggregate-function "count(*)") and AFAIK in no other relational database. I really cannot imagine any equivalent join-statement (or anything else like a subquery) which brings the same results! Does this at all correlate with the philosophy of a relational database? Best regards, Jens = Jens Hartwig - debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil: +49 (0)170 167-2648 E-Mail : [EMAIL PROTECTED] =
Re: Sv: [SQL] how to build this query ??? Please help !!!
Jens Hartwig <[EMAIL PROTECTED]> writes: > I have never seen a statement like > SELECT a, (SELECT b) > FROM xyz; > IMHO this is no normal subselect and it does not correlate with the > SQL-standard. Is it a trick? I think it's OK (we're assuming that a and b are columns of xyz, right?) The inner select sees values from the outer select as constants. This is not really different from SELECT x FROM xyz WHERE y IN (SELECT a FROM abc WHERE b = xyz.z); where the inner WHERE relies upon an outer reference to the current xyz tuple. It is true that "SELECT b" isn't a valid SQL92 SELECT because it hasn't got a FROM clause, but that's a minor quibble that just about every vendor has invented a workaround for. regards, tom lane
Re: Sv: [SQL] how to build this query ??? Please help !!!
> [...] > Subqueries are covered in Bruce Momjian's book: > http://www.postgresql.org/docs/aw_pgsql_book/node93.html > [...] Thanks for the information, but Bruce (which I have already read) and all other docs I´ve seen "only" mention normal subselects. I know subselects (what a poor database engineer I would be otherwise ;-)) but I have never seen a statement like SELECT a, (SELECT b) FROM xyz; IMHO this is no normal subselect and it does not correlate with the SQL-standard. Is it a trick? Best regards, Jens = Jens Hartwig - debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil: +49 (0)170 167-2648 E-Mail : [EMAIL PROTECTED] =
Re: Sv: [SQL] how to build this query ??? Please help !!!
Tod McQuillin <[EMAIL PROTECTED]> writes: > I don't think the PostgreSQL User's Manual mentions sub-selects. I think you are right :-(. Geez, that's a pretty big hole in the docs coverage. There is some stuff in the Tutorial --- see http://www.postgresql.org/devel-corner/docs/postgres/sql-language.htm#SELECT and page down to "Subqueries". Note that the subquery-in-FROM feature is new in 7.1. regards, tom lane
