R: Re: R: R: Re: [SQL] schema inspection
> >SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1, pg_class >c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid; > >for column(s) names you will have to do extra homework. Thanks! I have obtained my query! Here is: SELECT (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS fromTbl, (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS toTbl, (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid AND conkey[1]=attnum) AS viaCol FROM pg_catalog.pg_constraint AS rel WHERE contype='f'; 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 5: don't forget to increase your free space map settings
R: Re: R: Re: R: R: Re: [SQL] schema inspection
>Well thats it if you use only *single column* Foreign keys. >In the general case the above will need extra work. Are you sure? I have tested query with 3 table with multiple FK and it works (see below) [as you can see "C" table have two FK to A and B] test=> SELECT test-> (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS fromTbl, test-> (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS toTbl, test-> (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid AND conkey[1]=attnum) AS viaCol test-> FROM pg_catalog.pg_constraint AS rel WHERE contype='f' ORDER BY fromTbl; fromtbl | totbl | viacol -+---+ b | a | ida c | a | ida c | b | 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 2: Don't 'kill -9' the postmaster
[SQL] rowcount for all tables
i have a beginner question and i have read the FAQs. is there a faster ways than select count(*) from foo; to get the rowcount for the tables in my db ? Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] how to get current recursion level in recursive trigger?
Hello all! How to get current recursion level in recursive trigger? Andrew Dolin. ---(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
Re: [SQL] connectby documentation
Hi "outdated" packets is unfortunately a big issue on Debian. If you want to have up-to-date apt-packages try www.backports.org Add one of the mirrors from the list to your sources.list, then run apt-get update and then try to install again ... :-) And you'll see, that you can install newer versions than 7.4 :-) bye Lucius ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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;
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 5: don't forget to increase your free space map settings
Re: [SQL] schema inspection
Pgadmin is a good GUI interface if you want that same info, but pg_catalog is a better tool -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Achilleus Mantzios Sent: Thursday, March 16, 2006 9:45 AM To: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Subject: 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 iCrossing Privileged and Confidential Information This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ---(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] update before drop causes OID problems in transaction?
On Sat, 18 Mar 2006, Tom Lane wrote:
IIRC you'd have to drop the underlying plpgsql function, not only
the trigger object that connects the function to a table. We cache
stuff with respect to the function.
Tom, sorry it took me a little while to make a test case. The test case is
attached. If the attachments don't get through to the mailing list, you can
grab the files here:
http://www.frostconsultingllc.com/testcase/transaction-test-case-setup.sql
http://www.frostconsultingllc.com/testcase/transaction-test-case.sql
transaction-test-case-setup.sql will create the appropriate tables and
transaction-test-case.sql will demonstrate the error.
You can reproduce the problem like so:
createdb testcase
createlang plpgsql testcase
psql -f doc/perpetual/transaction-test-case-setup.sql testcase
psql -f doc/perpetual/transaction-test-case.sql testcase
psql:transaction-test-case.sql:10: ERROR: could not open relation with OID
2038878
I stripped the tables and queries down to the minimum that demonstrated the
error. Interestingly, the problem was not reproducible until I added the
credit_card_audit_account_id constraint below:
CONSTRAINT credit_card_audit_account_id_fkey FOREIGN KEY (account_id)
REFERENCES accounts_basics (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
Now that I've got a test case for you guys to look at, I'm off to rewrite our
standard procedure to use TRUNCATE instead of DROP.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954BEGIN;
CREATE TABLE accounts_basics
(
id SERIAL PRIMARY KEY,
country text NOT NULL DEFAULT 'US'::text,
email text NOT NULL,
password_reset bool DEFAULT false,
public_handle varchar(32) NOT NULL,
valid_email bool NOT NULL DEFAULT false,
modified_by int4,
event_type text
);
CREATE TABLE credit_card_type
(
id SERIAL PRIMARY KEY,
credit_card_type text NOT NULL,
CONSTRAINT ck_cc_type UNIQUE (credit_card_type)
);
CREATE TABLE credit_card
(
id SERIAL PRIMARY KEY,
account_id int4 NOT NULL,
profile_id int4 NOT NULL,
expires date NOT NULL,
active bool,
cc_number varchar(64),
credit_card_type text NOT NULL,
billing_name varchar(30) NOT NULL,
modified_by int4,
event_type text,
CONSTRAINT "$1" FOREIGN KEY (account_id)
REFERENCES accounts_basics (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT fk_cc_type FOREIGN KEY (credit_card_type)
REFERENCES credit_card_type (credit_card_type) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE credit_card_audit
(
credit_card_audit_id SERIAL PRIMARY KEY,
actor text NOT NULL DEFAULT "current_user"(),
"action" text NOT NULL,
credit_card_action_time timestamptz NOT NULL DEFAULT now(),
event_type text,
modified_by int4,
account_id int4,
credit_card_old credit_card,
credit_card_new credit_card,
CONSTRAINT credit_card_audit_action_check CHECK ("action" = 'INSERT'::text OR
"action" = 'UPDATE'::text OR "action" = 'DELETE'::text),
CONSTRAINT credit_card_audit_modified_by_fkey FOREIGN KEY (modified_by)
REFERENCES accounts_basics (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT credit_card_audit_account_id_fkey FOREIGN KEY (account_id)
REFERENCES accounts_basics (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
);
CREATE OR REPLACE FUNCTION audit_credit_card ()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
rows_affected INTEGER;
BEGIN
PERFORM tablename
FROM pg_tables
WHERE tablename = TG_RELNAME || '_audit';
IF NOT FOUND THEN
RAISE EXCEPTION 'No audit table found for %', TG_RELNAME;
END IF;
IF TG_OP = 'INSERT' THEN
INSERT INTO public.credit_card_audit (action, event_type, modified_by,
credit_card_new)
VALUES ('INSERT', NEW.event_type, NEW.modified_by, NEW );
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO public.credit_card_audit (action, event_type, modified_by,
credit_card_old, credit_card_new)
VALUES ('UPDATE', NEW.event_type, NEW.modified_by, OLD , NEW );
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO public.credit_card_audit (action, event_type, modified_by,
credit_card_old)
VALUES ('DELETE', OLD.event_type, OLD.modified_by, OLD );
ELSE
RAISE EXCEPTION 'TG_OP is none of INSERT, UPDATE or DELETE.';
END IF;
GET DIAGNOSTICS rows_affected = ROW_COUNT;
IF rows_affected = 1 THEN
IF TG_OP IN ('INSERT', 'UPDATE') THEN
RETURN NEW;
ELSE
RETURN OLD;
END IF;
ELSE
RAISE EXCEPTION 'INSERT failed on public.credit_card_audit';
END IF;
END;
$$;
CREATE TRIGGER audit_credit_card
BEFORE INSERT OR UPDATE OR DELETE
ON credit_card
FOR EACH ROW
EXECUTE PROCEDURE audit_credit_card();
CREATE INDEX credit_card_audit_event_type
Re: [SQL] rowcount for all tables
On Fri, Mar 17, 2006 at 14:39:04 +0100, Stefan Meyer <[EMAIL PROTECTED]> wrote: > i have a beginner question and i have read the FAQs. > is there a faster ways than > > select count(*) from foo; > > to get the rowcount for the tables in my db ? There are some ways to get approximate counts that won't require scanning the whole table. If you are going to be doing lots of these, you can use triggers to maintain a count. There is also a more complicated approach that can be used to reduce contention. You should be able to find more details in the archives. ---(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
