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

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
hunterex
  • 525
  • 9
  • 21

0 Answers0