[Solved] Unexpected behaviour for custom type returned from a function
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Unexpected behaviour for custom type returned from a function

I have created a custom type

CREATE TYPE rc_test_type AS (a1 bigint);

and a function 

CREATE OR REPLACE FUNCTION public.rc_test_type_function(test_table character varying, dummy integer)
  RETURNS rc_test_type AS
$BODY$ 
    DECLARE
    ret rc_test_type;
    query  text;
    BEGIN
    query := 'SELECT count(*) from '  || test_table    ;
    EXECUTE query into ret.a1;  
    RETURN ret;
END $BODY$
  LANGUAGE plpgsql VOLATILE

If I run

SELECT * FROM rc_test_type_function('some_table', 1);

I get 

"a1"
1389

So far so good.

If I run

SELECT p FROM (SELECT rc_test_type_function('some_table', s.step) AS p 
FROM  some_other_table s) foo;

I get

"p"
"(1389)"
"(1389)"

since 'some_other_table' has just two records. Fine.

But then if I try

SELECT p.a1 FROM (select rc_test_type_function('some_table', s.step) AS p
FROM some_other_table s) foo;

I get the error

missing FROM-clause entry in subquery for table »p«

which I find strange since the subquery has not changed.

Two questions:

  1. Can anyone explain what's going on?
  2. How do I extract the field value a1 from the returned array?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
SELECT
        * 
    FROM
        rc_test_type_function('some_table',
        1)

Related Articles



* original question posted on StackOverflow here.