PostgreSQL: ERROR: $name$ is not a scalar variable

Question

There is a function that returns 3 parameters one of which is a composite type:

CREATE OR REPLACE FUNCTION f1(
  p_text text,
  OUT result_status_id smallint,
  OUT result_status_msg text,
  OUT result_my_type my_type
  )
  RETURNS record AS
$BODY$
--body here
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100

The composite type my_type looks like following:

CREATE TYPE my_type AS
   (d_real real,
    d_int1 integer,
    d_int2 integer,
    d_int3 integer,
    d_int4 integer,
    d_int5 integer,
    d_int6 integer,
    d_int7 integer,
    d_int8 integer,
    d_int9 integer,
    d_int10 integer,
    d_bool boolean,
    d_date date,
    d_text text
);

There is another function f2 that calls function f1 in its body:

CREATE OR REPLACE FUNCTION f2(
    p_text text
)
  RETURNS record AS
$BODY$
DECLARE
    l_status_id smallint;
    l_status_msg text;
    l_my_type my_type;
BEGIN
--some logic here

--this statement fails
  SELECT * FROM f1(p_text) 'x' 
  INTO l_status_id, l_status_msg, l_my_type;

--logic continues here
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;

The problem is that when executing sql with the function I receive the following error:

ERROR: "l_my_type" is not a scalar variable

How may one get a composite type object from another function?

Erwin Brandstetter · Accepted Answer · 2016-05-19 01:03:45Z

2

You are violating the rules. The manual:

where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.

A record or row variable cannot be part of multiple-item INTO list.
One way around this:

CREATE OR REPLACE FUNCTION f2(p_text text)
  RETURNS record AS
$BODY$
DECLARE
   r            record;
   l_status_id  smallint;
   l_status_msg text;
   l_my_type    my_type;
BEGIN
   SELECT *
   FROM f1(p_text) x  -- don't single quote 'x'
   INTO r;

   l_status_id  := r.result_status_id;
   l_status_msg := r.result_status_msg;
   l_my_type    := r.result_my_type;

  RETURN r; -- or whatever ..
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

There are more ways. Depends where you are going with this. I hardly ever return an anonymous record.

edited May 19, 2016 at 1:03

answered Aug 31, 2012 at 13:56

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Timofey Over a year ago

that is okay as well and it is without single quotes near x. Without alias functions also

Erwin Brandstetter Over a year ago

The important part is the explanation of the error you ran into (for you and the general public). As mentioned, there are multiple ways around it. I chose a different example after you already posted the simple assignment yourself.

Chris Travers · Accepted Answer · 2012-08-31 02:09:30Z

1

Composite types in tuples are one of those things which is supported partially and you run into odd issues (particularly with thins like storage but that's a story for another time). One way you could accomplish this is:

CREATE TYPE return_type_for_function AS (
       result_status_id smallint,
       result_status_msg text,
       result_my_type my_type
);

CREATE FUNCTION myfunc(....) RETURNS return_type_for_function ....

This is the way I have always done it. This is a little more mature than using OUT variables.

Here's a trivial example:

or_examples=# create table rel_examples.tabletest (id int);
CREATE TABLE

or_examples=# create table comp_table_test (test rel_examples.tabletest);
CREATE TABLE

or_examples=# create function test(int) returns comp_table_test
immutable language sql as $$
select row(row($1))::comp_table_test; $$;
CREATE FUNCTION
or_examples=# select test(1);
  test   
---------
("(1)")
(1 row)

answered Aug 31, 2012 at 2:09

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

1 Comment

Timofey Over a year ago

+1 for an acceptable solution, but I found the trick which you may find in my answer

Timofey · Accepted Answer · 2012-08-31 13:12:43Z

1

However, @Chris Travers has proposed an acceptable solution, in my case it was unfortunately not possible to introduce a new type for returning data from f1 function.

Nevertheless, one may call the function f1() in the function f2() and still get the data using the following syntax:

CREATE OR REPLACE FUNCTION f2(
    p_text text
)
  RETURNS record AS
$BODY$
DECLARE
    l_status_id smallint;
    l_status_msg text;
    l_my_type my_type;
    l_record record;
BEGIN
--some logic here

--this statement is okay now
  l_record = f1(p_text);
  l_status_id = l_record.result_status_id;
  l_status_msg = l_record.result_status_msg;
  l_my_type = l_record.result_my_type;

--logic continues here
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;

answered Aug 31, 2012 at 13:12

Timofey

2,5183 gold badges38 silver badges54 bronze badges

1 Comment

Erwin Brandstetter Over a year ago

This is the simpler form, yes. On a different note: The assignment operator in plpgsql is :=. The = operator is undocumented for the purpose. Consider this related question.

segasai · Accepted Answer · 2012-08-30 17:48:12Z

0

In your f2() function, you could do the select into a record variable and then extract what you need from it, e.g.:

CREATE OR REPLACE FUNCTION f2(p_text text )
  RETURNS record AS
$BODY$
DECLARE
    record_var record;
BEGIN
--this statement was failing
  SELECT * FROM f1(p_text) INTO record_var;

 SELECT l_my_type.result_my_type.d_int1; -- this should work

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;

answered Aug 30, 2012 at 17:48

segasai

8,5581 gold badge31 silver badges40 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: ERROR: $name$ is not a scalar variable

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related