[SQL] error code invalid_input_syntax
I wrote a function which generates SQL statements for INSERTs and UPDATEs, in which I cast the values to the correct datatype. Now I want to catch ERROR: invalid input syntax for integer: "i" but although I tried quite a few I can't find the right error code for this exception. Is it possible that this error can not be caught? Thanks Leo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] foreign keys with on delete cascade and triggers
Hello Tom,
> If you want the whole transaction rolled back, raise an error instead
> of returning NULL.
You're right, that's working. But now I have a slightly different problem.
I assume that the trigger which watches the cascaded deletions first
deletes the row in the monitored table and then deletes any dependant
foreign keys. Thus the "foreign key tree" is deleted in a top-down
manner. This hinders any triggers on delete queries in cascaded tables
to query the referenced table any longer, since the referenced row is
already deleted. The following code shows what I mean:
create table a ( i serial primary key, name text );
create table b ( f int references a on delete cascade );
create or replace function f() returns trigger as $$
DECLARE
n text;
BEGIN
SELECT name INTO n from a where i=OLD.f;
IF FOUND THEN
RAISE NOTICE '% deleted me', n;
END IF;
RETURN OLD;
END;
$$ language plpgsql;
create trigger b_del before delete on b for each row execute procedure f();
insert into a(name) values('Dirk');
insert into b select currval('a_i_seq');
insert into a(name) values('Tom');
insert into b select currval('a_i_seq');
delete from b where f=1; -- will raise the notice
delete from a where i=2; -- wont raise anything
If the "foreign key tree" would be deleted in a bottom-up (or
depth-first) manner the second delete would be able to retrieve the
row in table a.
Now I'd like to know if the current order of deletions in PostgreSQL
is intended in the top-down way or if that could be changed?
--
---> Dirk Jagdmann
> http://cubic.org/~doj
-> http://llg.cubic.org
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] foreign keys with on delete cascade and triggers
Dirk Jagdmann <[EMAIL PROTECTED]> writes: > Now I'd like to know if the current order of deletions in PostgreSQL > is intended in the top-down way or if that could be changed? Sorry, I don't see much chance of changing it. regards, tom lane ---(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] plpgsql triggers in rules
Hi, Chester, chester c young wrote: > is is possible for to have a "do instead" trigger on a view that is a > plpgsql function? Kinda. They're called "rules", not "triggers". See http://www.postgresql.org/docs/8.1/interactive/rules.html HTH, Schabi -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] exceptions in rules
Hi, chester, chester c young wrote: > is there any way within a rule to raise an exception? Oh, so you know about rules - why did you ask for them before? You can use a plsql function to raise, if you don't find an easier way. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] info is a reserved word?
I have two identical functions below, the only difference is I declared my variable name to be 'info' instead of 'stuff'. I could not find anywhere in the docs that 'info' has any special meaning. Did I miss it? create type my_info as ( a text, b text ); -- this works create or replace function my_stuff () returns my_info as $$ declare stuff my_info; begin stuff.a := 'hi'; stuff.b := 'there'; return stuff; end; $$ language plpgsql; create or replace function my_stuff () returns my_info as $$ declare info my_info; begin info.a := 'hi'; info.b := 'there'; return info; end; $$ language plpgsql; Evaluating this definition gives: psql:16: ERROR: syntax error at or near "info" at character 71 psql:16: LINE 4: info my_info; psql:16: ^ pg 8.1.1, OS X 10.4.3 John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] info is a reserved word?
On Thu, Jan 12, 2006 at 08:14:42PM -0500, John DeSoi wrote: > I have two identical functions below, the only difference is I > declared my variable name to be 'info' instead of 'stuff'. I could > not find anywhere in the docs that 'info' has any special meaning. 'info' only seems special in PL/pgSQL, presumably because it's one of the possible RAISE levels. You should also get an error if you try 'exception', 'warning', etc. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] info is a reserved word?
Michael Fuhr <[EMAIL PROTECTED]> writes: > 'info' only seems special in PL/pgSQL, presumably because it's one > of the possible RAISE levels. You should also get an error if you > try 'exception', 'warning', etc. plpgsql is not very good about reserving words "minimally", ie, not treating a word as a keyword outside the context where the keyword is meaningful. This could probably be fixed, or at least greatly reduced, with some flex/bison hacking. Anyone up for it? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] info is a reserved word?
On Thu, Jan 12, 2006 at 11:21:28PM -0500, Tom Lane wrote: > plpgsql is not very good about reserving words "minimally", ie, not > treating a word as a keyword outside the context where the keyword > is meaningful. > > This could probably be fixed, or at least greatly reduced, with some > flex/bison hacking. Anyone up for it? Possibly. Would it involve much more than what the main parser's grammar does with unreserved_keyword and friends? I suppose this ought to move to pgsql-hackers. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
