Re: Not my day :-( Another syntax error

On Thu, Dec 26, 2019 at 10:39:54AM -0700, David G. Johnston wrote:
> You should probably send that reply again using reply-to-all.
>
> Dave
>
>
> On Thu, Dec 26, 2019 at 10:38 AM stan <stanb(at)panix(dot)com> wrote:
>
> > On Thu, Dec 26, 2019 at 10:26:49AM -0700, David G. Johnston wrote:
> > > On Thu, Dec 26, 2019 at 9:33 AM stan <stanb(at)panix(dot)com> wrote:
> > >
> > > >
> > > > WITH inserted AS (
> > > > INSERT into project_cost_category
> > > > (category)
> > > > VALUES
> > > > ('MISC')
> > > > RETURNING
> > > > *
> > > > )
> > > > SELECT project_cost_category_key
> > > > INTO NEW.project_cost_category_key =
> > > > ( SELECT
> > > > project_cost_category_key
> > > > FROM
> > > > inserted )
> > > >
> > > >
> > > You have two SELECTs. The "inner" one has a FROM clause attached to it
> > > providing columns from the "inserted" CTE. The "outer" one doesn't have
> > a
> > > FROM clause and so doesn't have access to columns. The "outer" SELECT
> > > project_cost_category_key is thus invalid.
> > >
> >
> > INSERT into project_bom
> > (project_key, bom_name)
> > VALUES
> > (NEW.project_key , 'Main')
> > RETURNING
> > project_bom_key
> > )
> > SELECT project_bom_key INTO NEW.project_bom_key
> > = ( SELECT
> > project_bom_key
> > FROM inserted )
> > ;
> >
> > Which is working, to the best of my knowledge. BTW the oen I am having
> > trouble with originaly had:
> >
> > RETURBING project_cost_category_key
> >
> > Bit I changed that to * during my debuging efforts.
> >
> > Please tell me if I am looking at this worng.
> >
> > And thatnls for looking through my really long post

Turns out, you were correct, changed it to:

DROP FUNCTION default_cost_category() CASCADE;

CREATE FUNCTION default_cost_category()
RETURNS trigger AS $$
DECLARE _cost_category_key numeric;
BEGIN
/* ZZZZZ */
if NEW.project_cost_category_key IS NULL
THEN
/* DEBUG
RAISE NOTICE 'Called default_cost_category() and NEW.project_cost_category_key is NULL' ;
*/
_cost_category_key =
(
SELECT
project_cost_category_key
FROM
project_cost_category
WHERE
category = 'MISC'
)
;
/* DEBUG
RAISE NOTICE '_cost_category_key = %', _cost_category_key ;
*/
IF _cost_category_key is NULL
THEN
WITH inserted AS (
INSERT into project_cost_category
(category)
VALUES
('MISC')
RETURNING
*
)
SELECT project_cost_category_key
INTO NEW.project_cost_category_key FROM
( SELECT
project_cost_category_key
FROM
inserted ) AS project_cost_category_key
;
ELSE
NEW.project_cost_category_key = _cost_category_key;
END IF;
END IF;

return NEW;
END;
$$
LANGUAGE PLPGSQL
SECURITY DEFINER
-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
SET search_path = ica, "user" , public
VOLATILE ;

And all is well.

Thank you!

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