topic Re: Extension Postgres in Extensions

Query fail in Postgres Extension

ArneV — Fri, 19 Dec 2025 14:07:17 GMT

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?

Re: Extension Postgres

AntonPineiro — Mon, 24 Nov 2025 10:16:21 GMT

Hi,

Maybe you can try this extension path.

Best regards

Re: Extension Postgres

PacoPorro — Mon, 24 Nov 2025 10:43:34 GMT

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

Re: Extension Postgres

ArneV — Mon, 24 Nov 2025 11:51:26 GMT

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.

Re: Extension Postgres

ArneV — Mon, 24 Nov 2025 11:52:14 GMT

This dosent really explain why the SQL is not working.

Re: Extension Postgres

david_lopes — Mon, 24 Nov 2025 22:42:42 GMT

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

Re: Extension Postgres

ArneV — Tue, 02 Dec 2025 06:18:43 GMT

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

Re: Extension Postgres

ArneV — Tue, 02 Dec 2025 10:54:39 GMT

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.

Re: Extension Postgres

ArneV — Thu, 04 Dec 2025 09:20:06 GMT

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