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