Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE "categories" ("category_id" TEXT NOT NULL PRIMARY KEY);
INSERT INTO "categories" ("category_id") VALUES ('foo');
INSERT INTO "categories" ("category_id") VALUES ('bar');
1 rows affected

1 rows affected

select schema_name
from information_schema.schemata


schema_name


pg_catalog

information_schema

x_tablefunc

x_pg_trgm

x_pageinspect

fiddle_hdtseqwriovwecdyvpjv


CREATE OR REPLACE FUNCTION "has_mismatching_ids"(
  _ids TEXT[],
  _column_name TEXT,
  _table_name TEXT,
  _schema_name TEXT DEFAULT 'public'
)
    RETURNS BOOLEAN AS
$$
DECLARE
   res boolean;
BEGIN
  IF _ids IS NULL THEN
      RETURN false;
  END IF;
  

 EXECUTE format 
 ( 'SELECT  COUNT(*) = cardinality($1)
      FROM    %I
    WHERE  %I  = ANY($2)',
   _table_name,_column_name
  )  INTO res USING _ids,_ids;
  
  RETURN res;
 END
$$
LANGUAGE plpgsql
    STABLE
    SECURITY INVOKER;
SELECT  "has_mismatching_ids"(ARRAY['foo2'], 'category_id', 'categories', 'public');


has_mismatching_ids


f


SELECT  "has_mismatching_ids"(ARRAY['foo','bar'], 'category_id', 'categories', 'public');


has_mismatching_ids


t