SQL Errors when statement is within a function and key is a variable or expression

[GHNewbiee]

Hi again,

I have been using the module/extension with PG11 and always receive the following errors when I run any statement at 6th time which is NOW within a function.

• When I use a simple parameter; for example:

  CREATE OR REPLACE FUNCTION function_name(parameter text)
  RETURNS text
  LANGUAGE 'plpgsql'
  AS $function_name$
  DECLARE
      result   text;
  BEGIN
      SELECT value FROM table_name
        WHERE key = parameter::text INTO result;
      RETURN result;
  END;
  $function_name$;

  I get the following error:

  
  Query execution failed

Reason: SQL Error [XX000]: ERROR: unrecognized node type: 222 Where: SQL statement "SELECT value FROM table_name WHERE key = parameter::text INTO result" PL/pgSQL function function_name(text) line 4 at SQL statement

- When I use an **expression**; for example:

... SELECT value FROM table_name WHERE key = LOWER(parameter)::text INTO result; ...

then I get

Query execution failed

Reason: SQL Error [HV004]: ERROR: key is NULL Where: SQL statement "SELECT value FROM table_name WHERE key = LOWER(parameter)::text INTO result" PL/pgSQL function function_name(text) line 4 at SQL statement

The same statements run without error outside a function (after substituting, of course, `parameter` with a text e.g. `'my_text'`).

Please check and let me know if you get the same errors.

Note: `::text` is merely for emphasis.

**Tia**

PS: Both kind of errors are PG error codes. So, I suspect that there is not any problem with the current module. I just want first to be sure before I send a message to PG mailing lists.

[l-d-x]

subquery processing has changed in Postgres10 onwards. I put in a change that works without the "lower" call. For some reason when you use lower, pgsql flips it to an function expression on the 6th go instead of a constant. I'll have to figure out why, but in the meantime the change resolves "select * from table where key = (select 'constant')" type of queries.

[l-d-x]

This is fixed in the latest commit.

The problem seems to be that pgsql switches from a CONST sub-expression into a FUNCEXPR after a few goes, which I hadn't seen in 9.x.

Turning on debugging, the first 5 tries parses the where clause as:

INFO:  ___ redis_parse_where
INFO:  T_OpExpr =[1], leftargtype: 25, rightargtype: 25
INFO:  subexpr type 105: {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location -1 :constvalue 8 [ 32 0 0 0 102 111 111 100 ]}

After that, it becomes

INFO:  ___ redis_parse_where
INFO:  T_OpExpr =[1], leftargtype: 25, rightargtype: 25
INFO:  subexpr type 111: {FUNCEXPR :funcid 870 :funcresulttype 25 :funcretset false :funcvariadic false :funcformat 0 :funccollid 100 :inputcollid 100 :args ({PARAM :paramkind 0 :paramid 1 :paramtype 25 :paramtypmod -1 :paramcollid 100 :location 43}) :location 37}
INFO:  unable to process subexpr type 111: {FUNCEXPR :funcid 870 :funcresulttype 25 :funcretset false :funcvariadic false :funcformat 0 :funccollid 100 :inputcollid 100 :args ({PARAM :paramkind 0 :paramid 1 :paramtype 25 :paramtypmod -1 :paramcollid 100 :location 43}) :location 37}