Re: Function PostgreSQL 9.2

On 04/19/2016 03:23 PM, drum.lucas@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"();
>
>
>
>
>
>     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.klaver@aklaver.com