Обсуждение: Can I do this?
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
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 <stanb@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
On Thu, Jan 16, 2020 at 6:28 AM stan <stanb@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.
What happens if two transactions by/for the same user are committed at around the same point in time? The advantage of sequences is they're guaranteed to be unique, I'm not sure counting the number of previous records and adding one will always assure that.
--
Mike Nolan
it does not, but the odds the same user will run this command by this id in two different sessions at the same time are very low.
this type of code exist for PO,SO, Invoices, to assign the next line item # in many apps.
On Thu, Jan 16, 2020 at 10:40 AM Michael Nolan <htfoot@gmail.com> wrote:
On Thu, Jan 16, 2020 at 6:28 AM stan <stanb@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.What happens if two transactions by/for the same user are committed at around the same point in time? The advantage of sequences is they're guaranteed to be unique, I'm not sure counting the number of previous records and adding one will always assure that.--Mike Nolan