Re: FOR UPDATE - Mailing list pgsql-general

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