1
CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _missing_id TEXT)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
        from logs
        where json_col_a::text like '%$1%' or   -- <---- faulty
            json_col_b::text like '%$1%'        --- <----- faulty
        order by timestamp desc'
    , pg_typeof(_tbl_type))
   USING  _missing_id;
END
$func$ LANGUAGE plpgsql;


CREATE TABLE new_table AS TABLE logs WITH NO DATA;

SELECT * FROM data_of(NULL::new_table, '00000000-0000-0000-0000-000000000001');

Adapted from the answer here, I want to pass a GUID as a text into the SELECT query.

I convert the json_col_a, json_col_b from json to text.

Then, I check whether the _missing_id text is found in both columns above.

When I run, I got an error message saying:

ERROR: operator does not exist: unknown % new_table LINE 4: where diagnostics_context::text like '%$1%' or

What have I missed? Thanks

hunterex
  • 565
  • 11
  • 27
  • What's the purpose of the "tbl_type" parameter? –  Apr 27 '21 at 11:03
  • @a_horse_with_no_name, I want to pass the `RETURN QUERY` to the `tbl_type` parameter. I thought the `tbl_type` could get the return value of the `Select` query. Please correct me if I am wrong. – hunterex Apr 27 '21 at 11:05

0 Answers0