CODEWITHSUNDEEP
postgresqlplpgsql
Daniel L. VanDenBosch
Daniel L. VanDenBosch

Reputation: 2725

Check to see if a record exists postgres function

I am attempting to create a function that will determine if a record exists for our applications developers to help simplify things. 

CREATE FUNCTION records_exist(schema_name VARCHAR(255), table_name VARCHAR(255), field_name VARCHAR(255), field_value VARCHAR(255))
  RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE

    _schema_name ALIAS FOR $1;
  _table_name  ALIAS FOR $2;
  _field_name  ALIAS FOR $3;
  _field_value ALIAS FOR $4;
  _sql_string  VARCHAR(5000);
BEGIN
_sql_string= 'SELECT EXISTS(SELECT 1 FROM ' || _schema_name || '.' || _table_name || ' WHERE ' || _field_name || '=' || _field_value || ');';



    RETURN BOOLEAN EXECUTE _sql_string;
    --RETURN TABLE   EXECUTE _sql_string; doesn't work  
END
$$;

The following should work, but I keep getting ERROR: syntax error at or near "EXECUTE"

Please let me know the error of my ways.

Upvotes: 0

Views: 485

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

also, your dynamic string is bad, better use:

select format('select exists from %I.%I where %I = %L',schema_name,table_name,field_name, field_value) into _sql_string;

also, you realize your _field_value has no check for data type?..

so in short, it could be smth similar to:

db=# CREATE or replace FUNCTION records_exist(schema_name VARCHAR(255), table_name VARCHAR(255), field_name VARCHAR(255), field_value VARCHAR(255))
  RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
 _sql text;
 _b boolean;
BEGIN
 _sql := format('select exists (select null from %I.%I where %I = %L)',schema_name,table_name,field_name, field_value);
 execute _sql into _b;
 return _b;
END
$$;
CREATE FUNCTION
Time: 10.680 ms
db=# select * from records_exist('pg_catalog','pg_database','datname','postgres'); records_exist
---------------
 t
(1 row)

Time: 59.472 ms

Upvotes: 1

Related Questions