Search Postgresql Archives

Re: Function PostgreSQL 9.2

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

 



On Thu, May 5, 2016 at 1:22 PM, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> wrote:


On 6 May 2016 at 02:29, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys <haramrae@xxxxxxxxx> wrote:

> On 05 May 2016, at 8:42, drum.lucas@xxxxxxxxx wrote:

> The final function code is:
>
> CREATE OR REPLACE FUNCTION users_code_seq()
>    RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
>         IF NEW.code IS NULL THEN
>         SELECT client_code_increment INTO STRICT NEW.code FROM public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;

                                                                        ^^^^^^^
There's your problem. I'm pretty sure the keyword STRICT isn't valid there. It probably gets interpreted as a column name.


​No, its a sanity check/assertion.  If that trips its because there is no company having a value of NEW.id on the public.companies table.  If that is OK then remove the STRICT but if you are indeed expecting a record to be present and it is not it is correctly telling you that there is a problem in the data.  Namely that said company needs to be added to the table.

David J.​
 


Taking off the "STRICT", the errors were gone. But still, it's not working. Please have a look below. 

​So, the error messages are gone - the underlying error still exists.​
 

 
If I use the other table:

CREATE TABLE public.company_seqs
(company_id BIGINT NOT NULL,
last_seq BIGINT NOT NULL DEFAULT 1000,
CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
);

It works fine.. the problem is when I try to use the companies table.. which is already there and I just add another column named: client_code_increment

haven't found the problem yet...

​You make this hard to help without a fully self-contained example for people to read.

​Berend already identified the problem for you.

1) You attached users_code_seq() to a trigger on the users table.
2) You have a where clause:  company_id = NEW.id
3) NEW refers to users
4) NEW.id is obstensibly a USER ID
5) So you are basically saying: WHERE company_id = user_id
6) If you were to get match it would be entirely by accident - say because you used the same integer for both id values

Hope that helps.

David J.


[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