0

This is my rookie attempt to create a function where it would return True or False depending on whether a table exists in the db. Unfortunately it outputs an empty table, and no raised messages;

CREATE FUNCTION public.check_if_error_table()
  RETURNS void AS
$BODY$
DECLARE
  table_exists text;
BEGIN
table_exists := (SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'error_table'));
RAISE NOTICE '%',table_exists;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

SELECT public.check_if_error_table();

Empty Table as output of check_if_error_table() function

The variable structure inside this function is extracted from the second answer of this question :- Store Query Result in Variable

And the SELECT statement giving a True or False value is also from the second answer to this question :- SQL - How to check if a table exists in a given schema

I want to formulate a function like above because;

I was not able to make the original function I made below work. The scenario I wanted to make was - if the first table was created, a message would be returned saying 'table is created please check' in the python script where these functions would eventually be called.

Also if the first table did not exist the second table would be created. RAISE EXCEPTION stopped the whole function/transaction, and RAISE NOTICE message did not appear in the python script where it was called.

-- Function: public.insert_equal_geoms(text, text, text, text)

-- DROP FUNCTION public.insert_equal_geoms(text, text, text, text);

CREATE OR REPLACE FUNCTION public.insert_equal_geoms(
    param_1 text, -- tindex 
    param_2 text, --complete topo50 
    param_3 text, -- error table 
    param_4 text) -- spatially equal table 
  RETURNS void AS
$BODY$
DECLARE
   v_row_count integer;
BEGIN

v_row_count := NULL;

   ---make error table if there are any instances of geometries not being equal to topo50 1k layer 
EXECUTE format('
   DROP TABLE IF EXISTS public.%s;
   CREATE TABLE public.%s (
   geom geometry(MultiPolygon, 2193),indexname varchar(100),predicate varchar(250));

   INSERT INTO public.%s
   SELECT a.geom, b.indexname
   FROM public.%s as a
   LEFT JOIN public.%s as b ON ST_Equals(a.geom, b.geom)
   WHERE b.indexname IS NULL;',param_3,param_3,param_3,param_1,param_2);

GET DIAGNOSTICS v_row_count = ROW_COUNT;

IF v_row_count = 0 THEN

    EXECUTE format('DROP TABLE IF EXISTS public.%s;
       CREATE TABLE public.%s (
       geom geometry(MultiPolygon, 2193),indexname varchar(100),predicate varchar(250));

       INSERT INTO public.%s
       SELECT b.geom, b.tilename, ''Spatially Equal''
       FROM public.%s as a, public.%s as b
       WHERE ST_Equals(a.geom, b.geom);',param_4, param_4, param_4, param_1, param_2);

    RAISE NOTICE '%: Table updated.', timeofday()::timestamp;
ELSE
    RAISE NOTICE '%: % errors found. Check error table and resolve issues.', timeofday()::timestamp, v_row_count;
END IF;

END;
   $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.insert_equal_geoms(text, text, text, text)
  OWNER TO postgres;

SELECT public.insert_equal_geoms(public.sample1,public.sample2,public.sample3,public.sample4)
  • Not sure I understand the question. Are you asking how to fix the check_if_error_table() function? Or how to improve your second function (that doesn't use check_if_error_table)? – a_horse_with_no_name Feb 22 at 10:25
  • The check function would be better written like this: rextester.com/BDLB25254 – a_horse_with_no_name Feb 22 at 10:28
  • @a_horse_with_no_name thank you for the help with my first question. Is there any possibility you could help me with the second question? (The first is the start of a multi-function workaround) – Rose Feb 22 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.