[SQL] create function problem
Hi folks, I'm writing my first plpsql function in ages and I'm going blind trying to see why it won't create. The error message and the code are below. I'm guessing it's something blindingly obvious, but can someone please point it out to me. ta Gary The actual line number is the LANGUAGE line at the end of the file. goole=# \i project_delivery_date.sql psql:project_delivery_date.sql:42: ERROR: syntax error at or near ";" LINE 37: END; ^ goole=# \q [r...@stan t-cards]# cat project_delivery_date.sql -- vim: ft=sql et ai ic -- -- project_delivery_date() - project delivery date from existing dates CREATE FUNCTION project_delivery_date(date,date,date,date) RETURNS date AS $$ DECLARE eta_dealer ALIAS FOR $1; eta_customer ALIAS FOR $2; req_date ALIAS FOR $3; act_date ALIAS FOR $4; eta date; BEGIN IF act_date IS NOT NULL THEN return act_date; END IF; IF eta_dealer IS NOT NULL AND eta_customer IS NULL THEN eta := eta_dealer; ELSE IF eta_dealer IS NULL AND eta_customer IS NOT NULL THEN eta := eta_customer; ELSE IF eta_dealer IS NULL AND eta_customer IS NULL THEN eta := NULL; ELSE IF eta_dealer > eta_customer THEN eta := eta_dealer; ELSE eta := eta_customer; END IF; IF eta IS NOT NULL AND req_date IS NULL THEN RETURN eta; END IF; IF eta IS NULL AND req_date IS NOT NULL THEN RETURN req_date; END IF; IF eta IS NULL AND req_date IS NULL THEN RETURN NULL; END IF; IF eta > req_date THEN RETURN eta; END IF; RETURN req_date; END; $$ LANGUAGE 'plpgsql'; [r...@stan t-cards]# -- Gary Stainburn I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create function problem
Hello you badly use a IF statement. It's not C. Every IF must to finish with END IF this is IF .. THEN ELSEIF .. THEN .. ELSE END IF Regards Pavel Stehule 2010/12/30 Gary Stainburn : > Hi folks, > > I'm writing my first plpsql function in ages and I'm going blind trying to see > why it won't create. The error message and the code are below. I'm guessing > it's something blindingly obvious, but can someone please point it out to me. > > ta > > Gary > > The actual line number is the LANGUAGE line at the end of the file. > > goole=# \i project_delivery_date.sql > psql:project_delivery_date.sql:42: ERROR: syntax error at or near ";" > LINE 37: END; > ^ > goole=# \q > [r...@stan t-cards]# cat project_delivery_date.sql > -- vim: ft=sql et ai ic > -- > -- project_delivery_date() - project delivery date from existing dates > > CREATE FUNCTION project_delivery_date(date,date,date,date) RETURNS date AS $$ > DECLARE > eta_dealer ALIAS FOR $1; > eta_customer ALIAS FOR $2; > req_date ALIAS FOR $3; > act_date ALIAS FOR $4; > eta date; > > BEGIN > IF act_date IS NOT NULL THEN > return act_date; > END IF; > IF eta_dealer IS NOT NULL AND eta_customer IS NULL THEN > eta := eta_dealer; > ELSE IF eta_dealer IS NULL AND eta_customer IS NOT NULL THEN > eta := eta_customer; > ELSE IF eta_dealer IS NULL AND eta_customer IS NULL THEN > eta := NULL; > ELSE IF eta_dealer > eta_customer THEN > eta := eta_dealer; > ELSE > eta := eta_customer; > END IF; > IF eta IS NOT NULL AND req_date IS NULL THEN > RETURN eta; > END IF; > IF eta IS NULL AND req_date IS NOT NULL THEN > RETURN req_date; > END IF; > IF eta IS NULL AND req_date IS NULL THEN > RETURN NULL; > END IF; > IF eta > req_date THEN > RETURN eta; > END IF; > RETURN req_date; > END; > $$ LANGUAGE 'plpgsql'; > > [r...@stan t-cards]# > > -- > Gary Stainburn > I.T. Manager > Ringways Garages > http://www.ringways.co.uk > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create function problem
On 12/30/2010 09:35 AM, Pavel Stehule wrote: Hello you badly use a IF statement. It's not C. Every IF must to finish with END IF this is IF .. THEN ELSEIF .. THEN .. ELSE END IF Regards Pavel Stehule To follow up I think the OP was looking for ELSEIF not ELSE IF. Changing the ELSE IF to ELSEIF should fix it. -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create function problem
On Thursday 30 December 2010 17:47:09 Adrian Klaver wrote: > To follow up I think the OP was looking for ELSEIF not ELSE IF. Changing > the ELSE IF to ELSEIF should fix it. > Thanks guys. That'll teach me to read tutorials more carefully. -- Gary Stainburn I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] create role
Hi, I'm successfully executing the below: create role developer login; alter role developer set default_tablespace=dev; alter role developer set search_path=dev,staging, esave, support, email, public; grant select on members to developer; grant create on schema dev to developer; However, when I do this: psql (8.4.5, server 8.4.2) Type "help" for help. esave_dw=> \d members Did not find any relation named "members". esave_dw=> esave_dw=> \d esave.members Table "esave.members" Column|Type | Modifiers -+-+--- memberid| numeric | not null etc How can I get this so I don't have to preface the \d with the schema name every time? Thanks. Tony -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Thursday 30 December 2010 2:14:23 pm Tony Capobianco wrote: > Hi, > I'm successfully executing the below: > > create role developer login; > alter role developer set default_tablespace=dev; > alter role developer set search_path=dev,staging, esave, support, email, > public; > > grant select on members to developer; > grant create on schema dev to developer; > > However, when I do this: > psql (8.4.5, server 8.4.2) > Type "help" for help. > > esave_dw=> \d members > Did not find any relation named "members". > esave_dw=> > esave_dw=> \d esave.members > Table "esave.members" >Column|Type | Modifiers > -+-+--- > memberid| numeric | not null > etc > > How can I get this so I don't have to preface the \d with the schema > name every time? > > Thanks. > Tony Did you log out and then back in as developer? Per: http://www.postgresql.org/docs/9.0/interactive/sql-alterrole.html "The remaining variants change a role's session default for a configuration variable, either for all databases or, when the IN DATABASE clause is specified, only for sessions in the named database. Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line." -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
