Re: Not my day :-( Another syntax error

čt 26. 12. 2019 v 18:50 odesílatel stan <stanb(at)panix(dot)com> napsal:

>
> 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
>

why you use CTE there - it is useless there. INSERT INTO RETURNING should
be enough

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
>
>
>