> Why do you want to lock these results?
Because we are migrating very old pipeline where we save data in file texts. So we have decided to keep some logics inside perl code. That means to update a record:
1. we have to read/lock it
2. return the result to the application
3. the application does its business and return the new value to the database
4. we update the record.
> What do you mean "empty"? If you pass the empty string, the query will
> return rows that have the empty string in those columns. Note that the
> NULL value is not the same as the empty string. If the function is
> defined as STRICT (also spelled RETURNS NULL ON NULL INPUT), then the
> function is not even called if you pass NULL arguments, and it simply
> returns NULL without locking anything.
> return rows that have the empty string in those columns. Note that the
> NULL value is not the same as the empty string. If the function is
> defined as STRICT (also spelled RETURNS NULL ON NULL INPUT), then the
> function is not even called if you pass NULL arguments, and it simply
> returns NULL without locking anything.
By empty I mean: ''
> Not necessarily ... depends on what you want to happen.
It is a migration case so I was thinking to take the more safe way..
----------------------------
So I think the behaviour is clear to me now.
Thank you.
On Mon, Nov 28, 2016 at 4:37 PM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx> wrote:
said assemlal wrote:
> Hello,
>
> PG: 9.4
> CentOS 6
>
> I am writing functions to lock results.
>
> Let's take an example:
>
> CREATE OR REPLACE FUNCTION usp_locking_my_result($1 VARCHAR(50), $2
> VARCHAR(50))
> RETURNS TEXT AS $$
> SELECT value
> FROM my_table
> WHERE field1 = $1 AND field2 = $2 FOR UPDATE;
> $$ LANGUAGE SQL;
Why do you want to lock these results?
> What happens if one of those arguments are empty and database finds
> results? ( I think they will locked )
What do you mean "empty"? If you pass the empty string, the query will
return rows that have the empty string in those columns. Note that the
NULL value is not the same as the empty string. If the function is
defined as STRICT (also spelled RETURNS NULL ON NULL INPUT), then the
function is not even called if you pass NULL arguments, and it simply
returns NULL without locking anything.
> Should I check the inputs and raise an exception if there are empty ?
Not necessarily ... depends on what you want to happen.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
