Re: Function PostgreSQL 9.2

On 20 April 2016 at 10:38, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Tue, Apr 19, 2016 at 3:23 PM, drum(dot)lucas(at)gmail(dot)com <
> drum(dot)lucas(at)gmail(dot)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