Extension Postgres

[ArneV]

I have written an extension to try to monitor the content of a table in a postgres DB.

However it looks as if im a bit stupid..

When i try to run my SQL directly against the DB it works.

However when i put it into my build extension, it fails.

Query has failed: ERROR: function date_subtract(date, unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

sqlPostgres:

  - group: Latest data

    featureSet: "Latest"

    interval:

      minutes: 1 #Executed every minute

    ingest: metrics

    query: >

      Select

        *

      From

        business_alert

      where

        Created >= date_subtract (CURRENT_DATE, '5 minute')

      order by

        Type  desc

        fetch first 1 rows only

    metrics:

      - key: mytest.sql.extension.value

        value: col:Type

    dimensions:

      - key: stat_name

        value: const:latest

Why would it complain over date_subtract?

[AntonPineiro]

Hi,

Maybe you can try this extension path.

Best regards

[ArneV]

This dosent really explain why the SQL is not working.

[PacoPorro]

try with
WHERE created >= NOW() - INTERVAL '5 minutes'

[ArneV]

I will try this, however my frustration is that, i do have a SQL that can execute directly against my postgress.

But it does not work with the extension build following DT sugestion.

I will give the "Now" way a shoot.

[david_lopes]

You query is NOT valid PostgreSQL dialect, I don't know how you are making this work with some other tool, there is no such thing as "date_subtract" in postgres.

It could be that someone created a custom function, but that is not going to be available from the extension, you will need to provide the schema name where that function is defined.

You can check if that is the case by running

SELECT
    n.nspname as schema,
    p.proname as function_name,
    pg_get_function_arguments(p.oid) as arguments,
    pg_get_functiondef(p.oid) as definition
  FROM
    pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
  WHERE
    p.proname = 'date_subtract';

 
Then you would modify the extension to use schema_name.date_subtract(...)