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@panix.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@panix.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