[SQL] schema inspection
Hi, supposing to have a small DB: TABLE a ( id SERIAL PRIMARY KEY ); TABLE b ( id SERIAL PRIMARY KEY, idA INTEGER NOT NULL REFERENCES a(id) ); How can I inspect pg_schema/information_schema to "detect" the relation between "b" and "a" via "idB"? TIA Roberto Colmegna Tiscali ADSL 4 Mega Flat Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese! Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] schema inspection
O [EMAIL PROTECTED] έγραψε στις Mar 16, 2006 : > Hi, > > supposing to have a small DB: > > TABLE a ( > id SERIAL PRIMARY KEY > ); > > TABLE b ( > id SERIAL PRIMARY KEY, > idA INTEGER NOT NULL REFERENCES a(id) > ); > > How can I inspect pg_schema/information_schema to "detect" the > relation between "b" and "a" via "idB"? > pg_catalog.pg_constraint is your (only?) friend. > TIA > Roberto Colmegna > > > > > > Tiscali ADSL 4 Mega Flat > Naviga senza limiti con l'unica Adsl a 4 Mega di velocitΓ a soli 19,95 β¬ > al mese! > Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. > http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- -Achilleus ---(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
[SQL] help with function
Hello, I have 2 tables where each table has a column named "comments" and the tables are related as a one to many. I want to concatenate all the comments of the many side to the one side so I wrote the following plpgsql function to do so. CREATE OR REPLACE FUNCTION fixcomments() RETURNS int4 AS $BODY$ DECLARE mviews RECORD; i int4; BEGIN FOR mviews IN SELECT * FROM saleorder WHERE comments is not null and comments <> '' LOOP -- Now "mviews" has one record from saleorder EXECUTE 'UPDATE sale SET comments = ' || quote_ident(sale.comments) || quote_ident(mviews.comments) || ' WHERE sale.id = ' || quote_ident(mviews.sale_id); i := i + 1; END LOOP; RETURN i; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; I have the following error when I run this code: ERROR: missing FROM-clause entry for table "sale" CONTEXT: SQL statement "SELECT 'UPDATE sale SET comments = ' || quote_ident(sale.comments) || quote_ident( $1 ) || ' WHERE sale.id = ' || quote_ident( $2 )" PL/pgSQL function "fixcomments" line 11 at execute statement Doesn anybody know what I am doing wrong here ? Lacou. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] help with function
Hello > EXECUTE 'UPDATE sale SET comments = ' || Use PERFORM instead Alexey ---(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
Re: [SQL] help with function
> Hello, > > I have 2 tables where each table has a column named "comments" and the > tables are related as a one to many. I want to concatenate all the > comments of the many side to the one side so I wrote the following > plpgsql function to do so. > > > CREATE OR REPLACE FUNCTION fixcomments() >RETURNS int4 AS > $BODY$ > DECLARE > mviews RECORD; > i int4; > BEGIN > > FOR mviews IN SELECT * FROM saleorder WHERE comments is not null > and comments <> '' LOOP > > -- Now "mviews" has one record from saleorder > > EXECUTE 'UPDATE sale SET comments = ' || > quote_ident(sale.comments) || quote_ident(mviews.comments) > || ' WHERE sale.id = ' || quote_ident(mviews.sale_id); EXECUTE 'UPDATE sale SET comments = ''' || quote_ident(sale.comments || mviews.comment) || ''' WHERE sale.id = ''' || quote_ident(mviews.sale_id) || ; Does that help? > i := i + 1; > END LOOP; > > RETURN i; > END; > $BODY$ >LANGUAGE 'plpgsql' VOLATILE; > > > > I have the following error when I run this code: > > > ERROR: missing FROM-clause entry for table "sale" > CONTEXT: SQL statement "SELECT 'UPDATE sale SET comments = ' || > quote_ident(sale.comments) || quote_ident( $1 ) || ' WHERE sale.id = ' > || quote_ident( $2 )" > PL/pgSQL function "fixcomments" line 11 at execute statement > > > Doesn anybody know what I am doing wrong here ? > > Lacou. > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend ---(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
R: R: Re: [SQL] schema inspection
> pg_catalog.pg_constraint is your (only?) friend. I have already examintated this table without results. Seem not to be a "human-readable" table :( TIA Roberto Colmegna Tiscali ADSL 4 Mega Flat Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese! Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] group by function, make SQL cleaner?
Tom Lane wrote:
In this particular case you could say
... GROUP BY 1 ORDER BY 1;
"ORDER BY n" as a reference to the n'th SELECT output column is in the
SQL92 spec. (IIRC they removed it in SQL99, but we still support it,
and I think most other DBMSes do too.) "GROUP BY n" is *not* in any
version of the spec but we allow it anyway. I'm not sure how common
that notation is.
Thanks. Markus Bertheau also supplied this solution:
SELECT enddate, count(*) FROM (
SELECT date_trunc('day', endtime) AS enddate FROM eg_event WHERE
endtime >= '2006-01-01' and endtime < '2006-03-01') as foo
GROUP BY enddate
ORDER BY enddate
It brings up a question though: is there any way in Postgres to set a "quirks" or "standards" mode. Or get Postgres to log the compliance level of each command and command element used, e,g.:
ORDER BY n
SQL92
GROUP BY n
PSQL
(SELECT ...)
SQL99
SELECT
SQL99
count(...)
SQL99
date_trunc(string,...)
PQSL
It is so easy to get lazy and start shrink-wrapping code to the database. That's certainly why mysql SQL tends to be so non-portable
[SQL] About how to use "exception when ??? then "
Hello, I am using PostgreSQL 8.0.1. In a function, I try to use exception to catch sql errors: begin begin exception WHEN ??? THEN end; ... end; The place where I have ???, what I should put there please? e.g., 1. WHEN sqlcode = '02000' THEN 2. WHEN no_data then 3. other ways? From the 8.0 docs, I am not be able to find Constant values of all error codes. http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html Please enlighten me. Thanks, Ying ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] executing external command
Is there a way to execute an external i.e. system command from inside a pl/pgsql function? Alex ---(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
Re: [SQL] executing external command
am 16.03.2006, um 13:39:57 -0500 mailte [EMAIL PROTECTED] folgendes: > > > Is there a way to execute an external i.e. system command from inside a > pl/pgsql function? You can call a untrusted function (plperlu, plsh, ...) and inside this function you can call system commands. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] About how to use "exception when ??? then "
Emi Lu wrote: exception WHEN ??? THEN The place where I have ???, what I should put there please? e.g., 1. WHEN sqlcode = '02000' THEN 2. WHEN no_data then no_data See ch 35.7.5. "Trapping Errors" for an example 3. other ways? From the 8.0 docs, I am not be able to find Constant values of all error codes. http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html From the page: "The PL/pgSQL condition name for each error code is the same as the phrase shown in the table, with underscores substituted for spaces. For example, code 22012, DIVISION BY ZERO, has condition name DIVISION_BY_ZERO. Condition names can be written in either upper or lower case. (Note that PL/pgSQL does not recognize warning, as opposed to error, condition names; those are classes 00, 01, and 02.)" I don't know if any have changed in 8.1, but there is a list of the codes in that version of the docs. http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html -- Richard Huxton Archonet Ltd ---(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
Re: [SQL] About how to use "exception when ??? then "
Hi Pedro, |> The place where I have ???, what I should put there please? |> |> e.g., |> 1. WHEN sqlcode = '02000' THEN |> 2. WHEN no_data then |> 3. other ways? |> |> From the 8.0 docs, I am not be able to find Constant values of all |> error codes. |> http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html |> |> Please enlighten me. Maybe this link will help you better, as it has the Constants: http://developer.postgresql.org/docs/postgres/errcodes-appendix.html Then, some logic real life examples would be something like: (snip) EXCEPTION WHEN NOT_NULL_VIOLATION THEN RAISE WARNING 'Not null...'; WHEN OTHERS THEN RAISE NOTICE 'H [%,%]', SQLSTATE, SQLERRM; or I am using postgresql 8.0.1. The keyword "SQLSTATE" & "SQLERRM" did not work for me. But, I think I do need the two outputs "sql error code", and "sql error code statement". Errors I got are: syntax error at or near "SQLSTATE" at character 2613 LINE 58:RAISE NOTICE 'H [%,%]', SQLSTATE, SQL... Should I install any patches or do anything elese to have SQLSTATE and SQLERRM work for me? Ying ---(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
Re: [SQL] About how to use "exception when ??? then "
From the page: "The PL/pgSQL condition name for each error code is the same as the phrase shown in the table, with underscores substituted for spaces. For example, code 22012, DIVISION BY ZERO, has condition name DIVISION_BY_ZERO. Condition names can be written in either upper or lower case. (Note that PL/pgSQL does not recognize warning, as opposed to error, condition names; those are classes 00, 01, and 02.)" That means pl/pgsql will not recognize error codes under classes 00, 01, 02. Is there a way, I can output error code? exception when ... then when others then raise notice '%, %', SQLSTATE, SQLERRM; But it seems that SQLERRM and SQLSTATE did not work for me. By the way, I am using postgresql 8.0.1. Thanks a lot, Ying ---(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
Re: [SQL] About how to use "exception when ??? then "
On Thursday 16 March 2006 19:32, Emi Lu wrote: |> Errors I got are: |> syntax error at or near "SQLSTATE" at character 2613 |> LINE 58:RAISE NOTICE 'H [%,%]', SQLSTATE, SQL... |> |> |> Should I install any patches or do anything elese to have SQLSTATE and |> SQLERRM work for me? I'm so sorry, i had a terrible day and in the rush to reply to you, i forgot to mention the patch i applied some time ago. The patch and thread talking about it can be found here: http://archives.postgresql.org/pgsql-patches/2005-04/msg00123.php This was what i used in my 8.0.6, and it worked fine ever since. :) Thanks a lot Pedro. Could you help me how to apply this patch such as the steps to load the patch please? By the way, I am using postgresql 8.0.1. I think the patch will work for all 8.0.x version, right? Thanks again, Ying ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] About how to use "exception when ??? then "
Emi Lu <[EMAIL PROTECTED]> writes: > Should I install any patches or do anything elese to have SQLSTATE and > SQLERRM work for me? Update to 8.1 ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] group by function, make SQL cleaner?
this should work,
#
SELECT date_trunc('day',endtime),count(*)
FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
GROUP BY 1
ORDER BY 1;
hope this helps
best regards,
Stefan
Am Donnerstag, 16. März 2006 06:18 schrieb Bryce Nesbitt:
> I've got a working query:
>
> stage=# SELECT date_trunc('day',endtime),count(*)
> FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
> GROUP BY date_trunc('day',endtime)
> ORDER BY date_trunc('day',endtime);
>
> date_trunc | count
> -+---
> 2006-02-01 00:00:00 | 253
> 2006-02-02 00:00:00 | 245
> 2006-02-03 00:00:00 | 231
> 2006-02-04 00:00:00 | 313
> 2006-02-05 00:00:00 | 285
> 2006-02-06 00:00:00 | 194
> 2006-02-07 00:00:00 | 229
> 2006-02-08 00:00:00 | 239
> 2006-02-09 00:00:00 | 250
> 2006-02-10 00:00:00 | 245
> 2006-02-11 00:00:00 | 275
>
> Is there a way to eliminate the ugly repeated use of
> date_trunc('day',endtime)?
>
>
> ---(end of broadcast)---
> TIP 1: 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
--
email: [EMAIL PROTECTED]
tel : +49 (0)6232-497631
http://www.yukonho.de
---(end of broadcast)---
TIP 1: 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
