Ygthkb

What is the name of this perspective and how is it constructed?

3D buried view in Tikz

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

What is the reason behind this musical reference to Pinocchio in the Close Encounters main theme?

Why does this quiz question say that protons and electrons do not combine to form neutrons?

What does an unprocessed RAW file look like?

Variance of sine and cosine of a random variable

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

Why is quixotic not Quixotic (a proper adjective)?

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?

Can you wish for more wishes from an Efreeti bound to service via an Efreeti Bottle?

How bad is a Computer Science course that doesn't teach Design Patterns?

Do these large-scale, human power-plant-tending robots from the Matrix movies have a name, in-universe or out?

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

Why are "square law" devices important?

Can I combine Divination spells with Arcane Eye?

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

Is layered encryption more secure than long passwords?

What is formjacking?

Can a planet be tidally unlocked?

Can I do anything else with aspersions other than cast them?

Exploding Numbers

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

Why write a book when there's a movie in my head?

PostgreSQL Function not returning message in Python 2.7 Script using EXCEPT NOTICE

Algorithm for finding the longest prefixWhy cannot I call a table function in iSeries DB2 that I just created?Why would call to scalar function inside a Table Value Function be slower than outside the TVF?Slow fulltext search for terms with high occurenceHow to run ALTER TABLE on a list of tables sequentially in a functionpostgresql function index not being used by queryPostgres function index doesn't work correctly with regular expressionsProper way to index table columns which can be filtered in multiple waysCan't connect to PostgreSQL instance using PythonPostgreSQL Function: Returning Boolean Values depending on whether a table exists

0

I was not able to make the PostgreSQL function return the raised message in the python script it was being called from.

The scenario I wanted to make was:-

1) 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, and the second table would not be created.

2) If the first table did not exist the second table would be created. RAISE EXCEPTION stopped the whole function/transaction, and RAISE NOTICE although did the trick with the If STATEMENT in terms of creating the second table if the first existed or not, the 'warning' 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)

This is how this function was being called in the Python Script;

postgresql functions python except

share

asked 6 mins ago

RoseRose

11

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

I was not able to make the PostgreSQL function return the raised message in the python script it was being called from.

The scenario I wanted to make was:-

1) 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, and the second table would not be created.

2) If the first table did not exist the second table would be created. RAISE EXCEPTION stopped the whole function/transaction, and RAISE NOTICE although did the trick with the If STATEMENT in terms of creating the second table if the first existed or not, the 'warning' 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)

This is how this function was being called in the Python Script;

postgresql functions python except

share

asked 6 mins ago

RoseRose

11

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 | 

I was not able to make the PostgreSQL function return the raised message in the python script it was being called from.

The scenario I wanted to make was:-

1) 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, and the second table would not be created.

2) If the first table did not exist the second table would be created. RAISE EXCEPTION stopped the whole function/transaction, and RAISE NOTICE although did the trick with the If STATEMENT in terms of creating the second table if the first existed or not, the 'warning' 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)

This is how this function was being called in the Python Script;

postgresql functions python except

share

asked 6 mins ago

RoseRose

11

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.

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

share

asked 6 mins ago

RoseRose

11

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 6 mins ago

RoseRose

11

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 6 mins ago

RoseRose

11

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 6 mins ago

RoseRose

11