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: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Function PostgreSQL 9.2 |
Date: | 2016-04-19 22:38:05 |
Message-ID: | CAKFQuwY+uzjnxmSW1CxaNXQ13UVTJVg5Fhy8iTvW_G4qs22TmA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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...?
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | drum.lucas@gmail.com | 2016-04-19 22:47:12 | Re: Function PostgreSQL 9.2 |
Previous Message | drum.lucas@gmail.com | 2016-04-19 22:23:49 | Function PostgreSQL 9.2 |