From: | stan <stanb(at)panix(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Can I do this? |
Date: | 2020-01-16 12:28:41 |
Message-ID: | 20200116122841.GA25728@panix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am trying to create a function to automatically create a reference value
when a record is inserted into a table. I want the reference value to
consist of the user that is doing the insert, plus a couple of dates, plus
a sequence number, where the sequence number will increment every time a
given user inserts a record. because this sequence number is user specific,
my first thought is not to use a set of sequences for it, but to do this by
selecting the maximum sequence number that user has entered in the past.
So, I have a function that gets all the data, and concatenates it into a
string with the exception of the sequence. For that component, I have the
following test select that works.
SELECT NULLIF(regexp_replace(report_no, '\D','','g'), '')::numeric AS result
FROM
expense_report_instance
WHERE
/* NEW.project_key */ 123 = project_key;
But, when I add the requisite MAX clause, I get a syntax error.
SELECT MAX(NULLIF(regexp_replace(report_no, '\D','','g'), '')::numeric
FROM
expense_report_instance
WHERE
/* NEW.project_key */ 123 = project_key);
Is there a way I can make this work?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
From | Date | Subject | |
---|---|---|---|
Next Message | stan | 2020-01-16 14:43:20 | Can I drop a NOT NUL constrain on an existing table? |
Previous Message | Arnaud L. | 2020-01-16 11:09:15 | minimal wal_level on subscriber |