From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Function PostgreSQL 9.2 |
Date: | 2016-04-19 23:22:58 |
Message-ID: | 5716BDD2.9060001@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 04/19/2016 03:23 PM, 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"();
>
>
>
>
>
> CREATE OR REPLACE FUNCTION
> "public"."auto_generate_client_code_if_empty" () RETURNS "trigger"
> VOLATILE
> AS $dbvis$
> BEGIN
> END;
> $dbvis$ LANGUAGE plpgsql;
>
>
>
>
>
>
> CREATE TRIGGER "increment_client_code"
> BEFORE INSERT OR UPDATE ON users
> FOR EACH ROW
> EXECUTE PROCEDURE "auto_generate_client_code_if_empty"();
>
>
>
> But still can't do that works.. What Am I missing?
Information. eg.:
The schema for the tables.
Why is not just adding a DEFAULT value to the users.code not an option?
What the default code should be or how it is to be calculated?
What is increment_client_code?
Does increment_client_code relate to users or some other table, say clients?
>
> Cheers
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2016-04-20 00:59:47 | Re: Enhancement Request |
Previous Message | drum.lucas@gmail.com | 2016-04-19 22:47:12 | Re: Function PostgreSQL 9.2 |