Re: 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?


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
[email protected]