[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