Logo-amall

I am trying to create a computed postgres field to display the daily order number, that should start at 1 at each new date. I am struggling with the where logic, here is where I am now: ```sql CREATE OR REPLACE FUNCTION fn_daily_order_no(order_row orders) RETURNS text AS $function$ SELECT count(id) FROM orders WHERE date_created -- >= date_created date:00:00 (beginning of the day) AND date_created <= order_row.date_created $function$ LANGUAGE plpgsql ``` Any ideas?

Last active 4 months ago

5 replies

10 views

  • SD

    I am trying to create a computed postgres field to display the daily order number, that should start at 1 at each new date.
    I am struggling with the where logic, here is where I am now:

    CREATE OR REPLACE FUNCTION fn_daily_order_no(order_row orders)
 RETURNS text
AS $function$

SELECT count(id)
FROM orders
WHERE date_created -- &gt;= date_created date:00:00 (beginning of the day) AND date_created &lt;= order_row.date_created

$function$
 LANGUAGE plpgsql

    Any ideas?

  • SD

    ok solved it with something like this:

    CREATE OR REPLACE FUNCTION fn_daily_order_no2(order_row orders)
 RETURNS numeric immutable
AS $function$

SELECT count(*) + 1
FROM orders
WHERE date_created &gt; date_trunc('day', order_row.date_created) AND date_created &lt; order_row.date_created

$function$
 LANGUAGE sql
  • EL

    Computed fields are powerful!

  • SD

    I only introduced myself to sql and postgres functions yesterday, and man it is so powerful and cheap on computation that I'm thinking of moving a lot of the backend logic to postgres functions.

  • EL

    Yes. It’s the best

Last active 4 months ago

5 replies

10 views

Join conversation on discord