From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> |
Cc: | Alban Hertroys <haramrae(at)gmail(dot)com>, Berend Tober <btober(at)computer(dot)org>, Melvin Davidson <melvin6925(at)gmail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Function PostgreSQL 9.2 |
Date: | 2016-05-05 20:43:00 |
Message-ID: | CAKFQuwZy9CF5O+4EzgspJ-cF3S2-MmTTDQR-iSJWHe8pko+Y+Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, May 5, 2016 at 1:22 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:
>
>
> On 6 May 2016 at 02:29, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys <haramrae(at)gmail(dot)com>
>> wrote:
>>
>>>
>>> > On 05 May 2016, at 8:42, drum(dot)lucas(at)gmail(dot)com 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-05-05 20:47:34 | Re: Debian and Postgres |
Previous Message | rob stone | 2016-05-05 20:40:46 | Re: Debian and Postgres |