PostgreSQL 10 – Malformatted Array Literal When Using EXECUTE

postgresqlpostgresql-10

I am trying to create a function on Postgres 10 that would check whether all the given ids exist
in the target table.

CREATE OR REPLACE FUNCTION "public"."has_mismatching_ids"(
  _ids TEXT[],
  _column_name TEXT,
  _table_name TEXT,
  _schema_name TEXT DEFAULT 'public'
)
    RETURNS BOOLEAN AS
$$
DECLARE
  result TEXT[] := '{}';
  _query TEXT := '';
BEGIN
  IF _ids IS NULL THEN
      RETURN false;
  END IF;

  _query := CONCAT('SELECT UNNEST($1) EXCEPT SELECT "', _column_name, '" FROM "', _schema_name ,'"."', _table_name ,'"');
  EXECUTE _query INTO result USING $1;
  RAISE NOTICE 'Executed';

  IF array_length(result, 1) > 0 THEN
      RETURN true;
  END IF;

  RETURN false;
END;
$$
LANGUAGE plpgsql
    STABLE
    SECURITY INVOKER;

This is my function that returns correctly true if all the ids exist, but it throws an exception if any of
the ids does not exist.

CREATE TABLE "public"."categories" ("category_id" TEXT NOT NULL PRIMARY KEY);
INSERT INTO "public"."categories" ("category_id") VALUES ('foo');
INSERT INTO "public"."categories" ("category_id") VALUES ('bar');

This works

SELECT FROM "public"."has_mismatching_ids"(ARRAY['foo'], 'category_id', 'categories', 'public');
NOTICE:  Executed
 has_mismatching_ids 
---------------------
 f
(1 row)

but this fails to even

SELECT FROM "public"."has_mismatching_ids"(ARRAY['foo2'], 'category_id', 'categories', 'public');
ERROR:  malformed array literal: "foo2"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  PL/pgSQL function public.has_mismatching_ids(text[],text,text,text) line 14 at EXECUTE

and strangely this works also if I write a function that uses explicit names and without EXECUTE

SELECT UNNEST(ARRAY['foo', 'foo3']) EXCEPT SELECT "category_id" FROM "public"."categories";
unnest 
--------
 foo3
(1 row)

Why doesn't my dynamic function work when comparing to values that do not exist in the target table?

Best Answer

I think your function does not work because it does USING $1 instead of the parameter name (USING _ids). Also, the concatenation is better replaced with the format function. And you can use one query instead of array_length trick.

This should work:

CREATE OR REPLACE FUNCTION "public"."has_any_ids"(
  _ids TEXT[],
  _column_name name,
  _table_name name,
  _schema_name name DEFAULT 'public'
)
RETURNS BOOLEAN
STABLE
LANGUAGE plpgsql AS $$
/* Checks whether any given id exists in the target table. */
DECLARE
    _query TEXT;
    _result BOOLEAN;
BEGIN
    _query := format(
        'SELECT EXISTS(
            SELECT 1 FROM %I.%I t WHERE t.%I = ANY(%L::text[]))',
        _schema_name, _table_name, _column_name, _ids);
    EXECUTE _query INTO _result;
    RETURN _result;
END;
$$;

CREATE OR REPLACE FUNCTION "public"."has_all_ids"(
  _ids TEXT[],
  _column_name name,
  _table_name name,
  _schema_name name DEFAULT 'public'
)
RETURNS BOOLEAN
STABLE
LANGUAGE plpgsql AS
$$
/* Checks whether all given ids exist in the target table. */
DECLARE
    _query TEXT;
    _result BOOLEAN;
BEGIN
    _query := format(
        'SELECT NOT EXISTS(
            (SELECT u FROM unnest(%L::text[]) u)
            EXCEPT
            (SELECT t.%I::text FROM %I.%I t WHERE t.%I = ANY(%L::text[]))
        )',
        _ids, _column_name, _schema_name, _table_name, _column_name, _ids
    );
    EXECUTE _query INTO _result;
    RETURN _result;
END;
$$;
Related Question