Обсуждение: BUG #13767: EXECUTE querytext USING value1, value2, value3 (Edge case?)
BUG #13767: EXECUTE querytext USING value1, value2, value3 (Edge case?)
От
needthistool@gmail.com
Дата:
The following bug has been logged on the website: Bug reference: 13767 Logged by: Seldom Email address: needthistool@gmail.com PostgreSQL version: 9.2.14 Operating system: Linux 4.1.10-17.31.amzn1.x86_64 #1 (~RHEL 4.8.3-9) Description: -- Attempt to run the following on any database, no setup necessary. -- Creating relations with the correct names etc. should not be needed, -- as the error occurs before the system has a chance to find any relations absent. -- The code below fails *in an unexpected way* because no substitution appears to take place. -- The correct behavior would be to throw an error stating that only INSERT, UPDATE, DELETE, -- and SELECT (DML) statements should be used in combination with the EXECUTE ... USING construct, -- perhaps recommending that FORMAT function be used instead. -- -- ERROR: syntax error at or near "$1" -- LINE 3: special_constraint_trigger($1,$2,$3); -- ^ -- SQL state: 42601 -- Context: PL/pgSQL function inline_code_block line 11 at EXECUTE statement -- DO LANGUAGE plpgsql $$ DECLARE -- simulated parameters: referencing_table TEXT = 'the_great_referencer'; referencing_column TEXT = 'fk_field'; referenced_column TEXT = 'measurement_id'; -- :simulated parameters BEGIN EXECUTE 'CREATE CONSTRAINT TRIGGER except_if_changes_break_references_77 AFTER UPDATE OR DELETE ON measurement_unit DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE special_constraint_trigger($1,$2,$3);' USING referenced_column,referencing_table, referencing_column; END; $$;
Re: BUG #13767: EXECUTE querytext USING value1, value2, value3 (Edge case?)
От
"David G. Johnston"
Дата:
On Tue, Nov 10, 2015 at 10:39 AM, <needthistool@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13767 > Logged by: Seldom > Email address: needthistool@gmail.com > PostgreSQL version: 9.2.14 > Operating system: Linux 4.1.10-17.31.amzn1.x86_64 #1 (~RHEL 4.8.3-9) > Description: > > -- Attempt to run the following on any database, no setup necessary. > -- Creating relations with the correct names etc. should not be needed, > -- as the error occurs before the system has a chance to find any relatio= ns > absent. > > -- The code below fails *in an unexpected way* because no substitution > appears to take place. > -- The correct behavior would be to throw an error stating that only > INSERT, > UPDATE, DELETE, > -- and SELECT (DML) statements should be used in combination with the > EXECUTE ... USING construct, > -- perhaps recommending that FORMAT function be used instead. > > -- > -- ERROR: syntax error at or near "$1" > -- LINE 3: special_constraint_trigger($1,$2,$3); > -- ^ > -- SQL state: 42601 > -- Context: PL/pgSQL function inline_code_block line 11 at EXECUTE > statement > -- > > DO LANGUAGE plpgsql $$ > DECLARE > -- simulated parameters: > referencing_table TEXT =3D 'the_great_referencer'; > referencing_column TEXT =3D 'fk_field'; > referenced_column TEXT =3D 'measurement_id'; > -- :simulated parameters > > BEGIN > EXECUTE 'CREATE CONSTRAINT TRIGGER > except_if_changes_break_references_77 > AFTER UPDATE OR DELETE ON measurement_unit > DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE > PROCEDURE > special_constraint_trigger($1,$2,$3);' > USING referenced_column,referencing_table, > referencing_column; > > END; > $$; > > =E2=80=8BNot a bug and while I do not know enough to discern whether teachi= ng "CREATE CONSTRAINT" and other non-parameter-taking queries to treat strings that look like "$#" specially is easily doable it likely is not. It doesn't really have anything to do with EXECUTE other than it is the medium by which the user is passing an arbitrary command to the engine. =E2=80=8BMy quick glance to try and find where this is all documented was unfruitful so I would agree with a sentiment the the documentation could be improved. I would suggest a section within the chapter named "Queries" [1] named something like "Parameterized Queries" that covers this topic and cross-references the relevant areas elsewhere (e.g., PREPARE, EXECUTE). [1] http://www.postgresql.org/docs/9.4/static/queries.html The frequency of this problem hitting the list is low but I can see where it can be surprising to the uninitiated. Since it does error quickly and relatively precisely answering the occasional question and teaching the user that the system does not accept parameters for every query type ends up being the more expedience solution so don't be surprised if this usability enhancement request goes unfulfilled. David J.