Re: BUG #13767: EXECUTE querytext USING value1, value2, value3 (Edge case?)

On Tue, Nov 10, 2015 at 10:39 AM, <needthistool(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13767
> Logged by: Seldom
> Email address: needthistool(at)gmail(dot)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;
> $$;
>
>
​Not a bug and while I do not know enough to discern whether teaching
"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.

​My 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.