all_id.sql

/*

    https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557
    
    public.id_exists evaulate a table($1) column name($2) ~* id existence.
    public.data_id_get return any table($1) data where column name($2) ~* id and $2 = $3.

*/
CREATE OR REPLACE FUNCTION public.id_exists(regclass,name,integer)
    RETURNS bool STRICT
    LANGUAGE plpgsql
    SET search_path FROM    current
    AS $function$
DECLARE
    result bool;
BEGIN
    IF  NOT $2 ~* 'id' THEN
        RAISE EXCEPTION 'only evaulate table column name ~* id ';
    END IF; 
    

    IF NOT EXISTS (
        SELECT
        FROM
            pg_class pc
            JOIN pg_attribute pa ON pa.attrelid = pc.oid
        WHERE
            pa.attnum > 0
            AND pa.attisdropped IS FALSE
            AND pa.atttypid = ANY('{int8, int4, int2, numeric,float4,float8}'::regtype[])
            AND attname = $2) THEN
    RAISE EXCEPTION 'no column name % in %', $2, $1;
END IF;
    EXECUTE format('select (exists(select from %s where %I = %L))', $1, $2, $3) INTO result;
    RETURN result;
END
$function$;

-----------test-----------------------------------------------
drop table if exists testid;
create table testid(
        tid int GENERATED by default as IDENTITY primary key
        , tid1 int
        );
insert into testid(tid1)    
select g from generate_series(1,10) g, generate_series(1,3) s;

SELECT * FROM public.id_exists('testid', 'tid', NULL);
SELECT * FROM public.id_exists('testid', 'tid', 2);
SELECT * FROM public.id_exists('testid', 'tid', 1e4::int);
SELECT * FROM public.id_exists('testid', NULL, NULL);
SELECT * FROM public.id_exists(NULL, NULL, NULL);


CREATE OR REPLACE FUNCTION data_id_get(anyelement,name,int)
  RETURNS SETOF anyelement
  STRICT
  set search_path from current
  LANGUAGE plpgsql AS
$func$
BEGIN
    IF  NOT $2 ~* 'id' THEN
        RAISE EXCEPTION 'only evaulate table column name ~* id ';
    END IF; 

   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  %I =   %s
      ORDER  BY 1'
    ,pg_typeof($1),$2,$3);
END
$func$;

----------test---------------------------------------------
SELECT * FROM public.data_id_get(NULL::public.testid,'tid1', 10);
SELECT * FROM public.data_id_get(NULL::public.testid,'tid1', NULL);