Check to see if a record exists postgres function

Question

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.

Vao Tsun · Accepted Answer · 2018-05-29 17:28:58Z

1

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

edited May 29, 2018 at 17:28

answered May 29, 2018 at 16:03

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel L. VanDenBosch Over a year ago

It is throwing me an error 'column "execute" does not exist'

Kamil Gosciminski Over a year ago

@VaoTsun why don't you just execute format() into?

Vao Tsun Over a year ago

tried drag closer to original _sql_string= ' - no other purpose.

Collectives™ on Stack Overflow

Check to see if a record exists postgres function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related