Search Postgresql Archives

Re: Can I do this?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux