Ygthkb

Can a Hydra make multiple opportunity attacks at once?

Multiple null checks in Java 8

Why do we interpret the accelerated expansion of the universe as the proof for the existence of dark energy?

Cryptic cross... with words

How to know if I am a 'Real Developer'

How can a kingdom keep the secret of a missing monarch from the public?

Question: "Are you hungry?" Answer: "I feel like eating."

Spells that would be effective against a Modern Day army but would NOT destroy a fantasy one

Sets which are both Sum-free and Product-free.

Integral problem. Unsure of the approach.

Variance of sine and cosine of a random variable

How can I make my enemies feel real and make combat more engaging?

Did the characters in Moving Pictures not know about cameras like Twoflower's?

Can I legally make a website about boycotting a certain company?

Manager has noticed coworker's excessive breaks. Should I warn him?

Do the speed limit reductions due to pollution also apply to electric cars in France?

For the Circle of Spores druid's Halo of Spores feature, is your reaction used regardless of whether the other creature succeeds on the saving throw?

If I have Haste cast on me, does it reduce the casting time for my spells that normally take more than a turn to cast?

Not sure how to set up the Laplacian/Poisson Equation

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

What does @ mean in a hostname in DNS configuration?

Taking an academic pseudonym?

Why don't programs completely uninstall (remove all their files) when I remove them?

What does it mean when an external ID field follows a DML Statement?

PostgreSQL Function: Returning Boolean Values depending on whether a table exists

Postgresql function to create tablePostgreSQL function thinks table reference exists when it does notPostgreSQL: Pass table as argument in functionPostgreSQL 9.5, getting “cached plan must not change result type” errorUsing newly inserted values in trigger function in PostgreSQLcreating postgresql function with table as inputUsing a trigger function to constrain values according to those already in the table (postgresql)PostgreSQL: Store function pointers in tablePostgres : Truncate if exists in psql function with parameterHow to count boolean values in PostgreSQL?

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;

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;

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;

postgresql functions

share

asked 3 mins ago

RoseRose

1

New contributor

Rose is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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;

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;

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;

postgresql functions

share

asked 3 mins ago

RoseRose

1

New contributor

Rose is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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;

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;

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;

postgresql functions

share

asked 3 mins ago

RoseRose

1

New contributor

Rose is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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;

-- 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;

share

asked 3 mins ago

RoseRose

1

New contributor

Rose is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 3 mins ago

RoseRose

1

New contributor

Rose is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 mins ago

RoseRose

1

New contributor

Rose is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 mins ago

RoseRose

1