Re: [GENERAL] pl sql to check if table of table_name exists
Shaun Clements wrote: Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Thanks in advance Kind Regards, Shaun Clements -- A list of tables: SELECT schemaname, tablename FROM pg_tables; -- Returns true if a table exists: SELECT count(*)0 FROM pg_tables WHERE schemaname='...' AND tablename='...' -- Here's an untested function: CREATE OR REPLACE FUNCTION table_exists(TEXT, TEXT) RETURNS BOOLEAN AS ' DECLARE r RECORD; BEGIN SELECT INTO r count(*)0 AS exists FROM pg_tables WHERE schemaname='$1' AND tablename='$2' RETURN r.exists; END; ' LANGUAGE plpgsql STABLE; Check out http://www.postgresql.org/docs/8.0/static/catalogs.html for more info. Adam ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Disabling triggers in a transaction
Bruce Momjian wrote:
Geoffrey wrote:
Terry Lee Tucker wrote:
Tom,
Do you feel this is a safe method for disabling triggers in the rare cases
where one finds that it is prudent to do that? Do you think that the column,
reltriggers, is permanent fixture in pg_class? What is your advice on this?
I'd be quite interested in this as well. Can one depend on this column
in the future? Even if not, as long as one verifies it still exists, is
this a viable option for trigger control within a transaction?
(This guy Tucker comes up with some interesting stuff...) :)
It is the only known way to control triggers though it isn't regularly
tested by the developers.
There's another way, provided you're willing to modify your triggers.
If so, you can gain per-session control over any and all triggers and
functions.
For example, suppose I have a trigger that logs certain events, but I
also want to be able to turn off logging while I embezzle the
funds^H^H^H^H^H^H^H^H do maintenance. I still want the logging trigger
to work for other clients, just not mine.
You even get transaction support, so if you disable logging, then
rollback, logging will be turned back on in your next transaction.
Example:
BEGIN;
SELECT disable_logging();
UPDATE some_table ...;
if (some_error) {
// Don't have to remember to enable_logging()
ROLLBACK;
}
SELECT enable_logging();
COMMIT;
The catch is, my logging trigger must be changed to look like this:
BEGIN
IF logging_enabled() THEN
-- Do logging
END IF;
END;
It takes advantage of the fact that temporary tables can only be seen in
the session that creates them. You create a real 'session_vars' table
with default values and a flag that can tell you if you are looking at
the real or temporary table. Then copy it into a temporary table and
reset your flag to mark it as such. You can then update other flags in
your temporary table that are only seen by the current session. So,
when you disable_logging(), you'll get FALSE from logging_enabled(), but
all other sessions will get TRUE.
---
CREATE TABLE session_vars (
id INT PRIMARY KEY,
valueBOOL NOT NULL,
description CHAR(20)
);
---
INSERT INTO session_vars(id, value, description)
VALUES (1, TRUE, 'table is non-temp');
INSERT INTO session_vars(id, value, description)
VALUES (2, FALSE, 'logging enabled');
---
CREATE FUNCTION session_vars_is_real() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=1;'
LANGUAGE SQL STABLE;
---
CREATE FUNCTION setup_session_vars() RETURNS BOOLEAN AS '
BEGIN
-- We''ll only ever get TRUE from the real table ...
IF session_vars_is_real() THEN
EXECUTE \'CREATE TEMPORARY TABLE session_vars AS
SELECT * FROM session_vars\';
-- ... and FALSE from the temporary table
EXECUTE \'UPDATE session_vars SET value=FALSE WHERE id=1\';
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION logging_enabled() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=2;'
LANGUAGE SQL STABLE;
---
CREATE FUNCTION enable_logging() RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
PERFORM setup_session_vars();
IF NOT logging_enabled() THEN
UPDATE session_vars SET value=TRUE WHERE id=2;
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION disable_logging() RETURNS BOOLEAN AS '
BEGIN
PERFORM setup_session_vars();
UPDATE session_vars SET value=FALSE WHERE id=2;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---(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: [GENERAL] Disabling triggers in a transaction
[Here's my third attempt to post this. Sorry if it's a dup. (trip?)]
If you're willing to modify your triggers you can gain per-session
control over any and all triggers and functions.
For example, suppose I have a trigger that logs certain events, but I
also want to be able to turn off logging while I embezzle the
funds^H^H^H^H^H^H^H^H do maintenance. I still want the logging trigger
to work for other clients, just not mine. I also want transaction
support, so if I disable logging, then rollback, logging will be turned
back on in my next transaction. Like this:
Usage Example:
BEGIN;
SELECT disable_logging();
UPDATE some_table ...;
if (check_error()) {
// Don't have to remember to enable_logging()
ROLLBACK;
}
SELECT enable_logging();
COMMIT;
The catch is, my logging trigger must be changed to look like this:
BEGIN
IF logging_enabled() THEN
-- Do logging
END IF;
END;
It takes advantage of the fact that temporary tables can only be seen in
the session that creates them. You create a real 'session_vars' table
with default values and a flag that can tell you if you are looking at
the real or temporary table. Then copy it into a temporary table and
reset your flag to mark it as such. You can then update other flags in
your temporary table that are only seen by the current session. So,
when you disable_logging(), you'll get FALSE from logging_enabled(), but
all other sessions will get TRUE.
---
CREATE TABLE session_vars (
id INT PRIMARY KEY,
valueBOOL NOT NULL,
description CHAR(20)
);
---
INSERT INTO session_vars(id, value, description)
VALUES (1, TRUE, 'table is non-temp');
INSERT INTO session_vars(id, value, description)
VALUES (2, FALSE, 'logging enabled');
---
CREATE FUNCTION setup_session_vars() RETURNS BOOLEAN AS '
BEGIN
-- We''ll only ever get TRUE from the real table ...
IF session_vars_is_real() THEN
EXECUTE \'CREATE TEMPORARY TABLE session_vars AS
SELECT * FROM session_vars\';
-- ... and FALSE from the temporary table
EXECUTE \'UPDATE session_vars SET value=FALSE WHERE id=1\';
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION session_vars_is_real() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=1;'
LANGUAGE SQL STABLE;
---
CREATE FUNCTION logging_enabled() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=2;'
LANGUAGE SQL STABLE;
---
CREATE FUNCTION enable_logging() RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
PERFORM setup_session_vars();
IF NOT logging_enabled() THEN
UPDATE session_vars SET value=TRUE WHERE id=2;
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION disable_logging() RETURNS BOOLEAN AS '
BEGIN
PERFORM setup_session_vars();
UPDATE session_vars SET value=FALSE WHERE id=2;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION log_trigger() RETURNS trigger AS '
BEGIN
IF logging_enabled() THEN
--do_logging;
END IF;
RETURN NEW; --or something
' LANGUAGE plpgsql VOLATILE;
Hope that helps,
Adam Tomjack
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Can't delete - Need cascading update instead
For various reasons, I can't actually delete records from my database. Instead, I have a boolean 'active' field for each table. I need to implement something like cascading delete, but instead of deleting, I need to set active=false. I've googled and haven't found a solution. I had two ideas, neither of which worked out. One thing I tried is to set ON DELETE CASCADE for all of my foreign key constraints. Then I added a rule ON DELETE DO ALSO UPDATE ... and a BEFORE DELETE trigger to stop the actual deletion. Unfortunately, that also stops the cascade. My other idea involved an ON DELETE DO INSTEAD UPDATE ... rule and a BEFORE UPDATE PL/pgSQL trigger that manually implemented the cascading. The problem with that is that the only way I can find to generate an approproate UPDATE or DELETE statement is to create a string and then EXECUTE it, but I need values from the NEW or OLD records, which apparently aren't usable from an EXECUTE statement. I'll include my code at the end. I haven't looked into using C. If that's what it takes, it'll be faster for me to just do it client side. Are there any other potential server-side solutions that I'm missing? Thanks for reading, Adam Tomjack -- My failed update trigger: CREATE OR REPLACE FUNCTION my_cascading_deleter() RETURNS trigger LANGUAGE 'plpgsql' VOLATILE AS $BODY$ DECLARE r RECORD; r2 RECORD; r3 RECORD; i RECORD; sql TEXT; BEGIN IF NEW.active=false AND OLD.active=true THEN -- Loop over each table that references this one. FOR r IN SELECT child.relname AS child, child.oid AS childid, parent.oid AS parentid, c.conkey AS childkey, c.confkey AS parentkey FROM pg_constraint c JOIN pg_class child ON (child.oid=c.conrelid) JOIN pg_class parent ON (parent.oid=c.confrelid) WHERE contype='f' and parent.oid=TG_RELID LOOP sql := 'DELETE FROM '||r.child||' WHERE '; -- Loop over every column in the primary key FOR i IN 1 .. array_upper(r.childkey, 1) LOOP SELECT INTO r2 attname FROM pg_attribute WHERE attrelid=r.childid AND attnum=r.childkey[i]; SELECT INTO r3 attname FROM pg_attribute WHERE attrelid=r.parentid AND attnum=r.parentkey[i]; sql := sql || ' ' || r2.attname || '=OLD.' || r3.attname; END LOOP; EXECUTE sql; -- ERROR, doesn't understand the OLD record END LOOP; END IF; RETURN NEW; END; $BODY$ ; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
