[pgpool-general: 3445] Re: Stored Procedure to Insert Point Data (Without PostGIS)

Tatsuo Ishii ishii at postgresql.org
Thu Jan 29 15:25:15 JST 2015


Are you getting the error onely when using pgpool-II? Or you get the
error even if you directly connect to PostgreSQL?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi All,
> 
> I want to insert FLOAT values into data of type POINT via Stored Procedure
> 
> 
> *My Table is*
> 
> CREATE TABLE SCHOOL
> (
> ID SERIAL NOT NULL,
> NAME VARCHAR(450),
> GEOM point,
> LON DOUBLE PRECISION,
> LAT DOUBLE PRECISION,
> CONSTRAINT "LOI_PK" PRIMARY KEY(ID)
> );
> 
> 
> *My Stored Procedure is*
> 
> 
> CREATE OR REPLACE FUNCTION SP_INSERT_SCHOOL
> (
> p_name varchar,
> p_lon float,
> p_lat float,
>  out p_loiID INT
> )
> RETURNS INT AS $$
> DECLARE
> d_geom varchar;
> BEGIN
>  RAISE NOTICE 'Inside Trigger';
>  d_geom = '''(' || cast(p_lon as varchar(30)) || ', ' || cast(p_lat as
> varchar(30)) || ')''';
>  RAISE NOTICE 'After geom Concat % ', d_geom;
>  insert into SCHOOL
> (
> NAME,
> GEOM,
> LAT,
> LON,
> )
> values
> (
> p_name,
> d_geom,
> p_lat,
> p_lon,
> )
> RETURNING ID into p_loiID;
> 
> return;
> 
> END;
> $$ LANGUAGE plpgsql;
> 
> 
> 
> *Stored Procedure call from SQL - *
> 
> select SP_INSERT_LOI2('School 1',72.8771232223511,19.0543151664734);
> 
> 
> 
> *Error - *
> 
> ERROR:  column "geom" is of type point but expression is of type character
> varying
> LINE 22: d_geom,
>          ^
> HINT:  You will need to rewrite or cast the expression.
> 
> 
> 
> Please help me to cast my FLOAT values into POINT type


More information about the pgpool-general mailing list