On 20 April 2016 at 10:38, David G. Johnston <david.g.johns...@gmail.com>
wrote:
> On Tue, Apr 19, 2016 at 3:23 PM, drum.lu...@gmail.com <
> drum.lu...@gmail.com> wrote:
>
>> Hi all,
>>
>> I've got two tables:
>>
>> - users
>> - companies
>>
>> I'm trying to create a function that:
>>
>>
>> - if users.code is empty, it gives a default value
>> - And the increment_client_code in company should auto increment for
>> the next client code
>>
>> What I've done so far:
>>
>> DROP FUNCTION IF EXISTS client_code_increment_count();
>>> CREATE OR REPLACE FUNCTION "public"."client_code_increment_count" ()
>>> RETURNS TABLE("code" INT) AS
>>> $BODY$
>>> SELECT MAX(CAST(users.code AS INT)) FROM users WHERE users.code ~
>>> '^\d+$' AND company_id = 2
>>> $BODY$
>>> LANGUAGE sql;
>>> SELECT * FROM "client_code_increment_count"();
>>
>>
>>
> The need to do "WHERE users.code ~ '^\d+$' means your model is poorly
> specified.
>
>
>
>>
>>
>>
>> CREATE OR REPLACE FUNCTION "public"."auto_generate_client_code_if_empty"
>>> () RETURNS "trigger"
>>> VOLATILE
>>> AS $dbvis$
>>> BEGIN
>>> END;
>>> $dbvis$ LANGUAGE plpgsql;
>>
>>
>>
> It would be nice if you actually showed some work here...
>
>
>>
>>
>>> CREATE TRIGGER "increment_client_code"
>>> BEFORE INSERT OR UPDATE ON users
>>> FOR EACH ROW
>>> EXECUTE PROCEDURE "auto_generate_client_code_if_empty"();
>>
>>
>>
>
> I'd question the need to execute this trigger on UPDATE...
>
>
>
>
>>
>> But still can't do that works.. What Am I missing?
>>
>>
>
> The stuff that goes between "BEGIN" and "END" in
> auto_generate_client_code_if_empty...?
>
>
That's all I got David.. working on it and would like some help if
possible...
Lucas
