2

There is user defined type which consist of double precision type and integer. It looks like it

CREATE TYPE ZOOM_LVL AS ( eps FLOAT8, minptrs INTEGER );

This function is used to set the value to user defined type

CREATE OR REPLACE FUNCTION zoom_level( lvl ZOOM_LVL )
RETURNS ZOOM_LVL AS $$
  SELECT current_setting( 'zoom_level.' || lvl )::ZOOM_LVL;
$$ LANGUAGE sql;

And the value is set through the call to function like this:

CREATE OR REPLACE FUNCTION set_zoom_level( zlvl TEXT )
RETURNS VOID AS $$
DECLARE
  zoom_lvl ZOOM_LVL;
BEGIN
   CASE zlvl::INTEGER
     WHEN 0 THEN
       zoom_lvl := (0.5::FLOAT8, 1::INTEGER);
     WHEN 1 THEN
       zoom_lvl := (0.7::FLOAT8, 1::INTEGER);
     WHEN 2 THEN
       zoom_lvl := (0.7::FLOAT8, 1::INTEGER);
     ELSE 
       zoom_lvl := (0.7::FLOAT8, 1::INTEGER);
     END CASE;

EXECUTE format( 'SET zoom_level.lvl TO %I', zoom_lvl )
// I alose tried EXECUTE format( 'SET zoom_level.lvl TO %I', zoom_lvl )
USING zoom_lvl;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION calc_clusters_internal()
RETURNS TEXT AS $$
 DECLARE
   coordinates TEXT;
   srid INTEGER :=4326;
   zoom_lvl ZOOM_LVL;
 BEGIN

   EXECUTE format( 'SELECT zoom_level( ''lvl'' );' )
   INTO zoom_lvl;

   EXECUTE format( 'SELECT ST_AsText( St_Transform( geom, $1 ) ), cluster_id 
                    FROM ( SELECT ST_Union( cluster.coords ) AS geom, cluster_id 
                           FROM (  SELECT geom.name, geom.coord AS coords, 
                                  ST_ClusterDBSCAN( geom.coord, eps := $2::FLOAT8, minPoints := $3::INTEGER )
                              OVER() AS cluster_id 
                              FROM ( SELECT name, ghash AS coord
                                     FROM cluster_storage_ptr ) as geom ) AS cluster 
                              WHERE cluster_id IS NOT NULL 
                              GROUP BY cluster_id 
                              ORDER BY cluster_id DESC ) AS clusters;
          ')
   INTO coordinates
   USING srid, zoom_lvl.eps, soom_lvl.minptrs;

   RETURN coordinates;

   END;
   $$ LANGUAGE plpgsql
   IMMUTABLE;

And when I call calc_clusters_internal() method I get error message like this

ERROR:  invalid input syntax for type double precision: "(0.5,1)"
CONTEXT:  PL/pgSQL function calc_clusters_internal() line 10 at EXECUTE

I read through some articles and question here at StackExchange (this, this and this), also I tried play with data types. I tried to use FLOAT instead of FLOAT8, also tried to find maybe there is a need to convert text to digit but did not figure if it real needed.

How can I figure out what is the problem?

Additional info: It seems that the problem somewhere in soom_level(ZOOM_LVL) or set_zoom_level(text) methods.

2

In your first EXECUTE in calc_clusters_internal, you will have to either explicitly expand the returned ZOOM_LVL type to feed it back to zoom_level via SELECT INTO, or set the zoom_level variable directly to the typed record:

  • EXECUTE FORMAT('SELECT (zoom_level( ''lvl'' )).*') INTO zoom_lvl;
    or
    EXECUTE FORMAT('SELECT * FROM zoom_level( ''lvl'' ))') INTO zoom_lvl;

or

  • zoom_level := (SELECT (zoom_level( 'lvl' ));

Some notes:

  • the function zoom_level() needs a TEXT parameter to work
  • since you don't seem to pass parameters dynamically to the zoom_level function, there's no need for an EXECUTE FORMAT, or for a parameter at all if you always pass in lvl hardcoded as you do in calc_clusters_internal; of course, this may by dynamic in your real world scenario

  • I see where a 'global variable' may be helpful in some DB centered applications, but using the system configuration variables is not only clumsy, their state is session specific and not really safe for concurrent write access; I strongly suggest to have your application client pass in the necessary parameters to your functions, or read them from relations
| improve this answer | |
  • thank you for yout reply. answers with execute did not work and failed with the same error message: invalid input syntax for type double precision: "(0.7,1)". the last one did work but i had to explicitly specify the type. I mean like that zoom_lvl:= ( SELECT current_setting( 'zoom_level.lvl' ) )::ZOOM_LVL; Of course it would be better to use the version with execute but as the system is only under test now i could stay with the working example. Thank you for help – Dmitriy Kormulev Jun 25 at 10:01

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.