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 · Accepted solution]

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(...)

[ArneV]

I did try it with your SQL sugestion. And it does not complain anymore.

However i tried to add a dimension to it.

    dimensions:

      - key: stat_name

        value: const:latest

And i started to get the error:

Error in YAML configuration. Failed to parse extension YAML: Dimension value must be prefixed with one of: ["col:", "const:", "var:"] but the value is Timestamp, line: N/A, column: N/A; Datasource has exited and will be starting shortly

However as you can see no Timestamp in it.

It's as if some "old" code wasen't removed from what i was working with.

Kind of driving me nuts..

Will try to delete the extension.

[ArneV]

Finaly had time to test it, and yes this actualy does work.

[ArneV]

Well thats strange, im using DBeaver and execute it against a local DB. So somthing must be right.

It's also described by https://www.postgresql.org/docs/current/functions-datetime.html