0

I have the following query, which I've modified for my database tables/columns, but it is not working upon execution:

CREATE OR REPLACE FUNCTION delete_data_antique(resourceid integer) 
RETURNS TABLE(metrics_values_id int4) AS $$
BEGIN
  RETURN QUERY 
    delete from metrics_values 
    where resource_id = $1 
      and time < (current_timestamp - interval '38 day') 
      and id not in
       (select id 
        from (select distinct on (time_week) 
                id, time, date_trunc('week', time) time_week 
          from metrics_values
          where resource_id = $1 
            and time < (current_timestamp - interval '38 day') 
          order by time_week, time desc)
          as first_in_week_versions) 
    returning id; 
END;
$$ LANGUAGE 'plpgsql';

Error message is as follows

sql function creation error

I'm new to creating functions in SQL, and have been reading the docs, but not sure where/how it's not actually working.

Larry G. Wapnitsky
  • 1,216
  • 2
  • 16
  • 35
  • 4
    The problem is in your client program which cannot properly interpret semicolon. Use PgAdmin or psql if you do not know how to force your software to work correctly. – klin Jul 06 '18 at 13:09
  • 3
    Unrelated, but: you don't need PL/pgSQL for this. A plain `language sql` function would do –  Jul 06 '18 at 13:15
  • Which SQL client are you using? –  Jul 06 '18 at 13:15
  • See e.g. here https://stackoverflow.com/questions/30211053 or –  Jul 06 '18 at 13:20
  • Still relatively new to programming functions. Most of my work has been basic and structured queries in the past. SQL client is Heidi, which I know has experimental support. I'll look at the links provided. Will star when I figure it out and get it working. Thanks, @a_horse_with_no_name – Larry G. Wapnitsky Jul 06 '18 at 13:40

0 Answers0