Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

503
Views
Postgresql Trigger execute error

I created a trigger function. When I insert a row to table. I get an error. Geom, k1k, k20k and pro_clustur_id are default empty. When a row is inserted trigger is fired. How can I solve this problem?

Failed to execute SQL. Error: ERROR: function st_point(double precision, double precision) does not exist LINE 1: ...denem_project set geom = st_transform(st_setsrid(st_point(p... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: update denem.denem_project set geom = st_transform(st_setsrid(st_point(project_lng, project_lat), 4326), 500000) where id is not null and pro_cluster_id is null and project_lng is not null and project_lat is not null CONTEXT: PL/pgSQL function create_cluster_id() line 4 at SQL statement

CREATE OR REPLACE FUNCTION denem.create_cluster_id()
  RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        update denem.denem_project  set geom = st_transform(st_setsrid(st_point(project_lng, project_lat), 4326), 500000) where id is not null and pro_cluster_id is null and project_lng is not null and project_lat is not null ;
        update denem.denem_project t1 set k20k=t2.fish_id from spatial.fish_net_k20k t2 where id is not null and pro_cluster_id is null and st_intersects(t1.geom,t2.geom) and k20k is null  ;
        update denem.denem_project t1 set  k1k=t2.fish_id from spatial.fish_net_k1k  t2 where id is not null and pro_cluster_id is null and st_intersects(t1.geom,t2.geom) and k1k is null ;
        update denem.denem_project set pro_cluster_id= (id||''||k20k)::bigint where id is not null and pro_cluster_id is null and  k20k is not null; 
    END IF;
  RETURN NEW;
 END;
 $$ language 'plpgsql';

CREATE TRIGGER create_cluster_id
  AFTER INSERT
  ON denem.denem_project
  FOR EACH ROW
  EXECUTE PROCEDURE denem.create_cluster_id();
over 4 years ago · Santiago Trujillo
1 answers
Answer question

0

First, find out in which schema PostGIS is installed.

You can either do this in psql with

\dx postgis

or with the SQL query

SELECT n.nspname
FROM pg_extension x
   JOIN pg_namespace n
      ON x.extnamespace = n.oid
WHERE x.extname = 'postgis';

Once you know the schema name, create the trigger function with

CREATE FUNCTION ... RETURNS trigger SET search_path='schemaname' AS ...;

over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!