Postgres PLV8 function with 'RETURNS TABLE' throws 'ERROR: ReferenceError: $1 is not defined'

Question

Trying to return table from PLV8 stored procedure.

It fails to recognize input args i.e. $1 when the function is with RETURNS TABLE(...).

It works when function returns scalars:

psql# CREATE OR REPLACE function foo(integer) 
      RETURNS integer 
      LANGUAGE plv8 
      AS $$
         var a=$1;
         return a;
      $$;
CREATE FUNCTION
psql# SELECT * FROM foo(10);
 foo 
-----
  10
(1 row)

It also works if function RETURNS SETOF.

(Case 1) With custom data type:

psql# CREATE TYPE myrow as (bar int);
CREATE TYPE
psql# CREATE OR REPLACE function foo(integer) 
      RETURNS SETOF myrow
      LANGUAGE plv8 
      AS $$
         var a=$1;
         return {"bar": a};
         // Or alternatively:
         // return plv8.execute('SELECT ' + a +' AS bar');
      $$;
CREATE FUNCTION
psql# SELECT * FROM foo(10);
 bar 
-----
  10
(1 row)

(Case 2) With SETOF record:

psql# CREATE OR REPLACE function foo(integer) 
      RETURNS SETOF record
      LANGUAGE plv8 
      AS $$
         var a=$1;
         return {"bar": a};
         // Or alternatively:
         // return plv8.execute('SELECT ' + a +' AS bar');
      $$;
CREATE FUNCTION
psql# SELECT * FROM foo(10) AS xxx(bar int);
 bar 
-----
  10
(1 row)

But it doesn't seem to work with RETURNS TABLE:

psql# CREATE OR REPLACE FUNCTION foo(integer) 
      RETURNS TABLE(bar int)  
      LANGUAGE plv8 
      AS $$
         var a=$1;
         return {"bar": a};
         // Doesn't matter cause it doesn't make it here, 
         // but alternative 'return' also fails with same error:
         // return plv8.execute('SELECT ' + a + ' AS bar');
      $$;
CREATE FUNCTION

psql# SELECT * FROM foo(10);
ERROR:  ReferenceError: $1 is not defined
DETAIL:  foo() LINE 2: var a=$1;

Any idea why it's choking on $1?

I'm on Postgres 9.4.0 and Plv8 1.4.4.

It seems to work OK with named arguments, e.g. "CREATE OR REPLACE FUNCTION foo(a integer)" — Emery Lapinski, Aug 10 '15 at 20:19

score 2 · Answer 1 · answered Aug 10 '15 at 20:22

2

It seems to work OK with named arguments, e.g. "CREATE OR REPLACE FUNCTION foo(a integer)"

I was actually searching for how to return more than one value from a plv8 function. For future reference, OUT parameters seem to be the way to go, e.g.

CREATE OR REPLACE FUNCTION foo(a integer, OUT b integer, OUT c integer) 
RETURNS setof record
LANGUAGE plv8 
AS $$

return {b: a * 2, c: a * 10};

$$;

answered Aug 10 '15 at 20:22

Emery Lapinski

1,572
18
26

The question was "why doesn't it work with `RETURNS TABLE...`?". – Thalis K. Aug 11 '15 at 08:52
Must be a difference in the way they handle parameters for functions that RETURNS TABLE. The source code to plv8 is available, but my C is a little rusty. https://github.com/plv8/plv8 Is there a reason you can't use a named parameter instead of $1? – Emery Lapinski Aug 11 '15 at 23:45

score 0 · Answer 2 · answered Jan 13 '23 at 15:27

I think it can not work as RETURNS TABLE is some art of pre-compiled in plpgsql. You could change the return type within javascript on the fly and it would break the expected return type (RETURNS TABLE).

Here is another link to a Github issue if you're interested in:

https://github.com/plv8/plv8/issues/314

Postgres PLV8 function with 'RETURNS TABLE' throws 'ERROR: ReferenceError: $1 is not defined'

2 Answers2