1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • SQL command gives different result when done in function

    From neojustebelmont@gmail.com@21:1/5 to All on Mon May 29 02:07:56 2017
    Hello all.

    I have an SQL command that is somewhere like

    "SELECT value FROM table WHERE date = '2017-02-28' AND product_id = 100 LIMIT 1"

    There are multiple rows if I don't use limit. But for convenience's sake, I'll say I get the value row to be "A"

    Now I put that SQL into a function. And when the function is called, it instead gave out "C", which also fulfilled the date and product_id requirement.

    Is there a way I could get "A" no matter what? Hopefully without adding extra WHERE clauses

    Thank you.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Dimitri Fontaine@21:1/5 to neojustebelmont@gmail.com on Mon May 29 11:20:31 2017
    neojustebelmont@gmail.com writes:
    Now I put that SQL into a function. And when the function is called, it instead gave out "C", which also fulfilled the date and product_id requirement.

    Is there a way I could get "A" no matter what? Hopefully without adding
    extra WHERE clauses

    The only way to know in which order you get the rows in SQL is to use
    the ORDER BY clause. I don't think it makes any sense to use the LIMIT
    clause without an ORDER BY clause, apart from interactive data discovery
    at the prompt.

    --
    Dimitri Fontaine
    PostgreSQL DBA, Architecte

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From DFS@21:1/5 to neojustebelmont@gmail.com on Mon May 29 22:07:20 2017
    On 5/29/2017 5:07 AM, neojustebelmont@gmail.com wrote:
    Hello all.

    I have an SQL command that is somewhere like

    "SELECT value FROM table WHERE date = '2017-02-28' AND product_id = 100 LIMIT 1"

    There are multiple rows if I don't use limit. But for convenience's sake, I'll say I get the value row to be "A"

    Now I put that SQL into a function. And when the function is called, it instead gave out "C", which also fulfilled the date and product_id requirement.

    Is there a way I could get "A" no matter what? Hopefully without adding extra WHERE clauses

    Thank you.


    You could hard-code the Primary Key value(s) which return for A.

    Will a VIEW do the job just as well?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)