From 6da0c439ee9e3648b252ad1483f25bc6bd9034b6 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 20 Sep 2004 23:00:12 +0000 Subject: [PATCH] Change some of the existing plpgsql regression test cases so that they exercise dollar quoting and named function parameters. AFAICS we had no tests of either feature before. --- src/test/regress/expected/plpgsql.out | 42 ++++++++++++--------------- src/test/regress/sql/plpgsql.sql | 42 ++++++++++++--------------- 2 files changed, 38 insertions(+), 46 deletions(-) diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 50d72830fb..e1b57f9716 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -119,14 +119,14 @@ create trigger tg_room_ad after delete -- * BEFORE INSERT or UPDATE on WSlot -- * - Check that room exists -- ************************************************************ -create function tg_wslot_biu() returns trigger as ' +create function tg_wslot_biu() returns trigger as $$ begin if count(*) = 0 from Room where roomno = new.roomno then - raise exception ''Room % does not exist'', new.roomno; + raise exception 'Room % does not exist', new.roomno; end if; return new; end; -' language 'plpgsql'; +$$ language plpgsql; create trigger tg_wslot_biu before insert or update on WSlot for each row execute procedure tg_wslot_biu(); -- ************************************************************ @@ -159,18 +159,18 @@ create trigger tg_pfield_ad after delete -- * BEFORE INSERT or UPDATE on PSlot -- * - Ensure that our patchfield does exist -- ************************************************************ -create function tg_pslot_biu() returns trigger as ' +create function tg_pslot_biu() returns trigger as $proc$ declare pfrec record; rename new to ps; begin select into pfrec * from PField where name = ps.pfname; if not found then - raise exception ''Patchfield "%" does not exist'', ps.pfname; + raise exception $$Patchfield "%" does not exist$$, ps.pfname; end if; return ps; end; -' language 'plpgsql'; +$proc$ language plpgsql; create trigger tg_pslot_biu before insert or update on PSlot for each row execute procedure tg_pslot_biu(); -- ************************************************************ @@ -191,25 +191,25 @@ create trigger tg_system_au after update -- * BEFORE INSERT or UPDATE on IFace -- * - set the slotname to IF.sysname.ifname -- ************************************************************ -create function tg_iface_biu() returns trigger as ' +create function tg_iface_biu() returns trigger as $$ declare sname text; sysrec record; begin select into sysrec * from system where name = new.sysname; if not found then - raise exception ''system "%" does not exist'', new.sysname; + raise exception $q$system "%" does not exist$q$, new.sysname; end if; - sname := ''IF.'' || new.sysname; - sname := sname || ''.''; + sname := 'IF.' || new.sysname; + sname := sname || '.'; sname := sname || new.ifname; if length(sname) > 20 then - raise exception ''IFace slotname "%" too long (20 char max)'', sname; + raise exception 'IFace slotname "%" too long (20 char max)', sname; end if; new.slotname := sname; return new; end; -' language 'plpgsql'; +$$ language plpgsql; create trigger tg_iface_biu before insert or update on IFace for each row execute procedure tg_iface_biu(); -- ************************************************************ @@ -243,12 +243,10 @@ create trigger tg_hub_a after insert or update or delete -- ************************************************************ -- * Support function to add/remove slots of Hub -- ************************************************************ -create function tg_hub_adjustslots(bpchar, integer, integer) +create function tg_hub_adjustslots(hname bpchar, + oldnslots integer, + newnslots integer) returns integer as ' -declare - hname alias for $1; - oldnslots alias for $2; - newnslots alias for $3; begin if newnslots = oldnslots then return 0; @@ -262,7 +260,7 @@ begin values (''HS.dummy'', hname, i, ''''); end loop; return 0; -end; +end ' language 'plpgsql'; -- Test comments COMMENT ON FUNCTION tg_hub_adjustslots_wrong(bpchar, integer, integer) IS 'function with args'; @@ -589,11 +587,9 @@ create trigger tg_backlink_a after insert or update or delete -- * Support function to set the opponents backlink field -- * if it does not already point to the requested slot -- ************************************************************ -create function tg_backlink_set(bpchar, bpchar) +create function tg_backlink_set(myname bpchar, blname bpchar) returns integer as ' declare - myname alias for $1; - blname alias for $2; mytype char(2); link char(4); rec record; @@ -684,8 +680,8 @@ begin end if; return 0; end if; -end; -' language 'plpgsql'; +end +' language plpgsql; -- ************************************************************ -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink -- * - Ensure that the opponent correctly points back to us diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index a8951cd6ef..367a73986e 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -159,14 +159,14 @@ create trigger tg_room_ad after delete -- * BEFORE INSERT or UPDATE on WSlot -- * - Check that room exists -- ************************************************************ -create function tg_wslot_biu() returns trigger as ' +create function tg_wslot_biu() returns trigger as $$ begin if count(*) = 0 from Room where roomno = new.roomno then - raise exception ''Room % does not exist'', new.roomno; + raise exception 'Room % does not exist', new.roomno; end if; return new; end; -' language 'plpgsql'; +$$ language plpgsql; create trigger tg_wslot_biu before insert or update on WSlot for each row execute procedure tg_wslot_biu(); @@ -208,18 +208,18 @@ create trigger tg_pfield_ad after delete -- * BEFORE INSERT or UPDATE on PSlot -- * - Ensure that our patchfield does exist -- ************************************************************ -create function tg_pslot_biu() returns trigger as ' +create function tg_pslot_biu() returns trigger as $proc$ declare pfrec record; rename new to ps; begin select into pfrec * from PField where name = ps.pfname; if not found then - raise exception ''Patchfield "%" does not exist'', ps.pfname; + raise exception $$Patchfield "%" does not exist$$, ps.pfname; end if; return ps; end; -' language 'plpgsql'; +$proc$ language plpgsql; create trigger tg_pslot_biu before insert or update on PSlot for each row execute procedure tg_pslot_biu(); @@ -246,25 +246,25 @@ create trigger tg_system_au after update -- * BEFORE INSERT or UPDATE on IFace -- * - set the slotname to IF.sysname.ifname -- ************************************************************ -create function tg_iface_biu() returns trigger as ' +create function tg_iface_biu() returns trigger as $$ declare sname text; sysrec record; begin select into sysrec * from system where name = new.sysname; if not found then - raise exception ''system "%" does not exist'', new.sysname; + raise exception $q$system "%" does not exist$q$, new.sysname; end if; - sname := ''IF.'' || new.sysname; - sname := sname || ''.''; + sname := 'IF.' || new.sysname; + sname := sname || '.'; sname := sname || new.ifname; if length(sname) > 20 then - raise exception ''IFace slotname "%" too long (20 char max)'', sname; + raise exception 'IFace slotname "%" too long (20 char max)', sname; end if; new.slotname := sname; return new; end; -' language 'plpgsql'; +$$ language plpgsql; create trigger tg_iface_biu before insert or update on IFace for each row execute procedure tg_iface_biu(); @@ -304,12 +304,10 @@ create trigger tg_hub_a after insert or update or delete -- ************************************************************ -- * Support function to add/remove slots of Hub -- ************************************************************ -create function tg_hub_adjustslots(bpchar, integer, integer) +create function tg_hub_adjustslots(hname bpchar, + oldnslots integer, + newnslots integer) returns integer as ' -declare - hname alias for $1; - oldnslots alias for $2; - newnslots alias for $3; begin if newnslots = oldnslots then return 0; @@ -323,7 +321,7 @@ begin values (''HS.dummy'', hname, i, ''''); end loop; return 0; -end; +end ' language 'plpgsql'; -- Test comments @@ -700,11 +698,9 @@ create trigger tg_backlink_a after insert or update or delete -- * Support function to set the opponents backlink field -- * if it does not already point to the requested slot -- ************************************************************ -create function tg_backlink_set(bpchar, bpchar) +create function tg_backlink_set(myname bpchar, blname bpchar) returns integer as ' declare - myname alias for $1; - blname alias for $2; mytype char(2); link char(4); rec record; @@ -797,8 +793,8 @@ begin end if; return 0; end if; -end; -' language 'plpgsql'; +end +' language plpgsql; -- ************************************************************ -- 2.40.0