Testing set_eq on function that returns type table

[JedW ICD]

I'm trying to test the resultset of a function that returns table.

CREATE OR REPLACE function frp_pkg_notif.get_all_tenant_notifs
 ( p_status_list     text[],
   p_start_ts        timestamptz,
   p_stop_ts         timestamptz
 )
 returns table ( t_id bigint, t_customer_id bigint, t_tenant_name varchar,
                 t_notif_id bigint, t_notif_type varchar, t_ts_created timestamptz,
                 t_details jsonb, t_status varchar, t_status_msg jsonb, t_last_note text
               )
 SECURITY INVOKER
 LANGUAGE plpgsql
AS

...

I've been trying various things with set_eq and row_eq (though I get the impression from doc that row only checks for one row not multiple, but maybe reading it wrong.)

My latest attempt (switch out set_eq with row_eq) is
drop table if exists t_result_type;
CREATE table t_result_type (
  t_id bigint, t_customer_id bigint, t_tenant_name varchar,
  t_notif_id bigint, t_notif_type varchar, t_ts_created timestamptz,
  t_details jsonb, t_status varchar, t_status_msg jsonb, t_last_note text
);
select set_eq (
  $$ select frp_pkg_notif.get_all_tenant_notifs(array['open','wip','closed'],'2021-11-01','2021-11-30'); $$,
  ( ROW( 1, 2, 'frp_tenant2', 101, 'ingest', '2021-11-04 04:04:04',  '{"jed": 1}', 'closed', null, 'My first 101 note' )::t_result_type ),

  'TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2'
);

which results in:
ERROR:  cannot compare dissimilar column types record and bigint at record column 1
CONTEXT:  SQL statement "SELECT NOT rec IS DISTINCT FROM $2"
PL/pgSQL function row_eq(text,anyelement,text) line 6 at IF

I've tried this using a type instead of table and just $$VALUES ROW(...) $$ with no luck. 

I'm hoping I'm just doing something silly or stupid. 
any ideas?

I was able to get the has_function and function_returns to work just fine.

[Rory Campbell-Lange]

On 13/07/22, JedW ICD (jed.w...@icd-tech.com) wrote:
> I'm trying to test the resultset of a function that returns table.

...

> select set_eq (
> $$ select
> frp_pkg_notif.get_all_tenant_notifs(array['open','wip','closed'],'2021-11-01','2021-11-30');
> $$,
> ( ROW( 1, 2, 'frp_tenant2', 101, 'ingest', '2021-11-04 04:04:04',
> '{"jed": 1}', 'closed', null, 'My first 101 note' )::t_result_type ),
> 'TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for
> Tenant2'
> );
> which results in:
> ERROR: cannot compare dissimilar column types record and bigint at record

There are a few things here:

set_eq is used when you don't care about the order of results or there is the potential for duplicate rows.
https://pgtap.org/documentation.html#set_eq
You probably want results_eq if you are only checking one row
https://pgtap.org/documentation.html#results_eq

The second is that if you are providing literal values you probably want to provide those in something like the following format:

SELECT bag_eq (
$$
SELECT * FROM something
$$
,
$$
VALUES
-- n_shift , markers , summation
-- --------- , --------- , ----------
( 0 , ARRAY['m1','m2'] , 7::bigint)
, ( 0 , ARRAY['m1','m2'] , 99::bigint)
$$
,'my test name'
);

Note that in your example as you are returning a result table returning

t_id bigint
,t_customer_id bigint
,t_tenant_name varchar,

,t_notif_id bigint
,t_notif_type varchar
,t_ts_created timestamptz
,t_details jsonb
,t_status varchar
,t_status_msg jsonb
,t_last_note text

You will need to cooerce some of the literal data you are providing, for
example 1::bigint.

Rory

[JedW ICD]

Hi, I appreciate your help. I do need ability to have multiple rows and order does not matter so I was using set_eq for that reason. I tried casting the first column based on an error that said something on lines of "wrong type column 1". 
So, the issue seems to be that it is a type and not just a result-set, here is where I am at. 
select set_eq (
  $$ select * from ( select frp_pkg_notif.get_all_tenant_notifs(array['open','wip','closed'],'2021-11-01','2021-11-30')) result ; $$,
  $$VALUES

    ROW( 1, 2, 'frp_tenant2', 101, 'ingest', '2021-11-04 04:04:04',  '{"jed": 1}', 'closed', null, 'My first 101 note' )

  $$,

  'TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2'
);

ERROR:  column "get_all_tenant_notifs" has pseudo-type record
CONTEXT:  SQL statement "CREATE TEMP TABLE __taphave__ AS  select * from ( select frp_pkg_notif.get_all_tenant_notifs(array['open','wip','closed'],'2021-11-01','2021-11-30')) result ; "
PL/pgSQL function _temptable(text,text) line 3 at EXECUTE

I'll keep plugging away at it and still appreciate help.

[JedW ICD]

I've been trying several things. It returns a table and based on the error it is *I'm guessing* seen as an array of types, so I've tried both of these

select set_eq (
  $$ select frp_pkg_notif.get_all_tenant_notifs(array['open','wip','closed'],'2021-11-01','2021-11-30') ; $$,

  $$VALUES

    ( 1, 2, 'frp_tenant2', 101, 'ingest', '2021-11-04 04:04:04',  '{"jed": 1}', 'closed', null, 'My first 101 note' )
  $$,
  'TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2'
);

select set_eq (
  $$ select frp_pkg_notif.get_all_tenant_notifs(array['open','wip','closed'],'2021-11-01','2021-11-30') ; $$,

  ARRAY [

    ROW( 1, 2, 'frp_tenant2', 101, 'ingest', '2021-11-04 04:04:04',  '{"jed": 1}', 'closed', null, 'My first 101 note' )::t_result_type

  ],

  'TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2'
);

which result in the same error:

ERROR:  column "get_all_tenant_notifs" has pseudo-type record

CONTEXT:  SQL statement "CREATE TEMP TABLE __taphave__ AS  select frp_pkg_notif.get_all_tenant_notifs(array['open','wip','closed'],'2021-11-01','2021-11-30') ; "

PL/pgSQL function _temptable(text,text) line 3 at EXECUTE

So, somehow I have to figure out how to get  the correct type in my :result parameter I think.

?

[Robert L. Kirby]

The :sql arguments of results_eq, set_eq, and bag_eq must be statements (like SELECT, VALUES,  INSERT, or a prepared statement name) rather than expressions like "(ROW ...)". If an expression is used, it gets converted to text, which has only one column and doesn't match the first column of the record returned by the other argument.

Bob Kirby

[JedW ICD]

@bob that does make sense and I would have thought returning a table is essentially returning rows, but it doesn't like that. Is there a simple way to take that return set and convert it to the equivalent of a normal select?

[JedW ICD]

Ha, it finally hit me. I tried select * from ( select frp_pkg_notif.get.....) but of course that just returns the whole type for each row, I needed to specify each column to break it out into a normal select return.

See here:

select set_eq (
  $$ select t_id, t_customer_id t_tenant_name, t_notif_id, t_notif_type, t_ts_created, t_details, t_status, t_status_msg, t_last_note from frp_pkg_notif.get_all_tenant_notifs(array['open','wip','closed'],'2021-11-01','2021-11-30') ; $$,

  $$VALUES
    ( 1, 2, 'frp_tenant2', 101, 'ingest', '2021-11-04 04:04:04',  '{"jed": 1}', 'closed', null, 'My first 101 note' )
  $$,
  'TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2'
);

with result being

----------------------------------------------------------------------------------------------------------------------
 not ok 1 - TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2                                +
 # Failed test 1: "TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2"                        +
 #     Columns differ between queries:                                                                               +
 #         have: (bigint,bigint,bigint,character varying,timestamp with time zone,jsonb,character varying,jsonb,text)+
 #         want: (integer,integer,text,integer,text,text,text,text,text,text)
(1 row)

So, yay, now i just need to cast types correctly to match.

I think this is going to do the job easily.

Lots of appreciation to everyone who looked, thought, and gave thoughts.

[Robert L. Kirby]

VALUES makes statements out of literal expressions. The documentation examples subtly use "SELECT * FROM ..." rather than expressions.

Bob Kirby

--
You received this message because you are subscribed to a topic in the Google Groups "pgTAP Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/pgtap-users/kNBkTC-ApmA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to pgtap-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pgtap-users/a0a7007c-7ca2-4e45-9835-71022603ff5bn%40googlegroups.com.

[JedW ICD]

As above I finally figured out a way to make the results of the function table return look like a standard literal expression result from select by just including each column of the returned table. Worked great.

select set_eq (
  $$ select t_id, t_customer_id, t_tenant_name, t_notif_id, t_notif_type, t_ts_created,
            t_status, t_last_note
     from frp_pkg_notif.get_all_tenant_notifs(array['open','wip'],'2021-11-01','2021-11-30');
  $$,
  $$VALUES
    ( 1::bigint, 2::bigint, 'frp_tenant2'::varchar, 101::bigint, 'ingest'::varchar, '2021-11-04 04:04:04'::timestamptz,  'open'::varchar, 'My first 101 note'::varchar )

  $$,
  'TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2'
);

                                      set_eq
----------------------------------------------------------------------------------
 ok 4 - TEST-01A: frp_pkg_notif.get_all_tenant_notifs Check 1st notif for Tenant2
(1 row)
also, posted on it here: https://tinky2jed.wordpress.com/technical-stuff/postgresql/pgtap-set_eq-on-function-returning-table-error-column-has-pseudo-type-record/