Postgres how can I create a function to use inside a query

Question

I am relatively new to postgres and am using version 9.4 . I have a simple function in Java that I been told would be more efficient if I let postgres do it. My function takes an Integer and a String data that comes from the database and transforms them .This is my function: Notice it takes an Integer and a String

private static String difference(Integer i,String created_on) {
    String id = "";
    if (i < 60) {
      ...
        }
        else if (i >= 60 && i < 1440) {

       ....
    } 
else if (i >= 1440 && i < 10080) {

      ....

    } 
    else {

      ....
    }

    return id;
}

This is my query now the Created_on is the String and last_reply is the Integer

"SELECT created_on as created_on,
last_reply as last_reply;

Once the data comes from the database I put it in a loop and transform it by doing this:

  for(i=0;i<10;i++) 
      {
                jo = new JSONObject();
                jo.put("last_reply", difference(rs.getInt("last_reply",
                                    rs.getString("created_on"))));
            }

As you can see the data transformation happens here

difference(rs.getInt("last_reply", rs.getString("created_on")

My question now is how can I replicate that function and save it in postgres so that I can do a query like this

**

**"SELECT difference(last_reply,created_on) as newvalue, created_on as created_on,
    last_reply as last_reply;**

** As I know that from a performance standpoint it is better to have the database do that rather than loop through the data using Java. Any suggestions would be great ...

Update

The function is used for a social app that measures the time something is posted in terms of Minutes. If it is less than 60 minutes then it will return something like "6 mins ago etc", if it is between 60-1440 then it will return " 4 hrs ago etc." . I have already worked out the calculations inside the If-else statements but did not include it so that the code looks more presentable.

Here is the full Method

private static String difference(Integer i,String created_on) {
    String id = "";
    if (i < 60) {

        if(i<1)
            id = "Now";

        else
            id = i + " min";
        }
        else if (i >= 60 && i < 1440) {

        i=(i/60);
        if(i==0)
        {i=1;}
        id = i + " hrs";
    } else if (i >= 1440 && i < 10080) {

        i=(i/1440);
        id = i + " day(s)";

    }
    else {
  // Posted longer than 7 days so Created_On will show date created
 // Passed down to the id String
        id = created_on;

    }

    return id;
}

What's the logic of your difference() method? You're just showing a lot of nonsensical if clauses and the String parameter isn't used anywhere. — Kayaman
– Kayaman, Commented Feb 4, 2016 at 18:13
It measures the time in terms of minutes less than 60 is min from 60-1440 returns hrs plus the number. I'll update it. — user1949387
– user1949387, Commented Feb 4, 2016 at 18:20
What does the String created_on do? Why do you pass it as a parameter if you're not using it anywhere? You have a lot of text in your question, but very little information. — Kayaman
– Kayaman, Commented Feb 4, 2016 at 18:25
The created_on is used in the last else Block. If something has been posted longer than 7 days then the Created_on takes in the Date that something was created such as 1-20-2016 . I will post the full Method code above . — user1949387
– user1949387, Commented Feb 4, 2016 at 18:31
I'd clean up the logic first. You're first checking if(i < 60) then you recheck if(i < 1) (i.e. if i == 0). You're also doing a redundant check for i==0 in the second else, it can never happen since i is at least 60. Then instead of creating a stored procedure I'd just go with a CASE -THEN. — Kayaman
– Kayaman, Commented Feb 4, 2016 at 18:45

JustBeingHelpful · Accepted Answer · 2016-02-04 18:33:35Z

2

Not sure what the data types are for the Java difference function, but you can do it one of two ways. Pull the data set into Java from Postgres, and find the difference there by passing the two values into your function, and then use that result set for whatever purpose.

The other way is to create a function in Postgres directly and then cal that function from your query.

create function:

CREATE FUNCTION intDifference(integer, integer) RETURNS integer
    AS 'select $2 - $1;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

using function in query:

SELECT intDifference(last_reply,created_on) as newvalue, created_on as created_on, last_reply as last_reply
FROM SOME_TABLE;

answered Feb 4, 2016 at 18:33

JustBeingHelpful

19.1k39 gold badges169 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres how can I create a function to use inside a query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related