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

[Dave Chetan]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20150127/df141c50/attachment.htm>