Hi Stan
in you code sample there are "(" mis-matched, "MAX(" matches to "=
project_key)";
it should be
MAX(NULLIF(regexp_replace(report_no, '\D','','g'), '')::numeric)
I do exactly what you do, and you are correct sequences are not a good fit
I typically do something like this for
select 'username' || 'more text' || Max(count)+1::text from
myreport_counter_table group by userName, report_id where userName = 'the
user ' and report_id = 12
On Thu, Jan 16, 2020 at 7:28 AM stan <st...@panix.com> wrote:
> 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
>
>
>
