And I thought I had this solved.

On 11/22/19 3:52 PM, stan wrote:

A while back I ran into problems caused by security fix related to the
search path. I wound up adding a line to. for instance, this function:

REATE FUNCTION
work_hours
(
start_date date,
end_date date
)
RETURNS decimal(10,4) stable
language sql as $$

/* workaround for secuirty "feature" */
SET search_path TO ica, "user" , public;

SELECT
sum(case when
extract(isodow from d)
between 1 and 5 then
8.0 else
+0.0 end)
from

generate_series($1,
$2, interval
'24 hours') d;

$$;

And walked away happy, or so I thought. Now I just got this error:

[local] stan@stan=# select * from ttl_avail_hours_by_project_and_employee ;
ERROR: SET is not allowed in a non-volatile function
CONTEXT: SQL function "work_hours" during startup

How can I solve this issue?

If the above is the entire function I am not seeing that you need to SET
search_path as there is no object that needs schema qualification. I
would comment it out and run it.

If that fails then set the function volatile.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/22/19 3:52 PM, stan wrote:

A while back I ran into problems caused by security fix related to the
search path. I wound up adding a line to. for instance, this function:

REATE FUNCTION
work_hours
(
start_date date,
end_date date
)
RETURNS decimal(10,4) stable
language sql as $$

/* workaround for secuirty "feature" */
SET search_path TO ica, "user" , public;

SELECT
sum(case when
extract(isodow from d)
between 1 and 5 then
8.0 else
+0.0 end)
from

generate_series($1,
$2, interval
'24 hours') d;

$$;

And walked away happy, or so I thought. Now I just got this error:

[local] stan@stan=# select * from ttl_avail_hours_by_project_and_employee ;
ERROR: SET is not allowed in a non-volatile function
CONTEXT: SQL function "work_hours" during startup

How can I solve this issue?

I thought I was missing something. Third option. As example:

https://www.postgresql.org/docs/11/sql-createfunction.html

Writing
SECURITY DEFINER
Functions Safely

...

$$ LANGUAGE plpgsql
SECURITY DEFINER
-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
SET search_path = admin, pg_temp;

Put the SET outside the function body.
--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, Nov 22, 2019 at 04:06:14PM -0800, Adrian Klaver wrote:

On 11/22/19 3:52 PM, stan wrote:

A while back I ran into problems caused by security fix related to the
search path. I wound up adding a line to. for instance, this function:

REATE FUNCTION
work_hours
(
start_date date,
end_date date
)
RETURNS decimal(10,4) stable
language sql as $$

/* workaround for secuirty "feature" */
SET search_path TO ica, "user" , public;

SELECT
sum(case when
extract(isodow from d)
between 1 and 5 then
8.0 else
+0.0 end)
from

generate_series($1,
$2, interval
'24 hours') d;

$$;

And walked away happy, or so I thought. Now I just got this error:

[local] stan@stan=# select * from ttl_avail_hours_by_project_and_employee ;
ERROR: SET is not allowed in a non-volatile function
CONTEXT: SQL function "work_hours" during startup

How can I solve this issue?

I thought I was missing something. Third option. As example:

https://www.postgresql.org/docs/11/sql-createfunction.html

Writing
SECURITY DEFINER
Functions Safely

...

$$ LANGUAGE plpgsql
SECURITY DEFINER
-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
SET search_path = admin, pg_temp;

Put the SET outside the function body.

OH, that seems the cleanest way to do this.

Thanks.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin