Thread: There is error at the examples in PL/pgSQL
Hello,all
I use your a example of PL/pgSQL, but there are some errors when I execute
these codes. The details are followings,
First, I create a exam.sql that includes these codes as followings,
CREATE TABLE emp (
empname text,
salary int4,
last_date datetime,
last_user name);
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS'
BEGIN
-- Check that empname and salary are given
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF;
-- Who works for us when she must pay for?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := ''now'';
NEW.last_user := getpgusername();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Secondly, I execute exam.sql and the postgress can create the table emp,
the function emp_stamp() and the trigger emp_stamp seccessfully.But when I
insert one record to table emp, there are some errors on the screen.
the insert statement is followings,
INSERT INTO emp Values('','','20001220','raymond');
the error of screen is:
NOTICE: plpgsql: ERROR during compile of emp_stamp near line 1
"RROR: parse error at or near "
Why? and what wrong is it? Please give me reply as possible as you can.
Thanks!
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
You probably copied it from a machine that uses CR/LF rather than LF as
newline. I think 7.1 handles this but earlier ones will fail. Trim
the CR characters and it should work.
On Mon, 5 Mar 2001, Lu Raymond wrote:
> Secondly, I execute exam.sql and the postgress can create the table emp,
> the function emp_stamp() and the trigger emp_stamp seccessfully.But when I
> insert one record to table emp, there are some errors on the screen.
> the insert statement is followings,
> INSERT INTO emp Values('','','20001220','raymond');
>
> the error of screen is:
> NOTICE: plpgsql: ERROR during compile of emp_stamp near line 1
> "RROR: parse error at or near "
>
> Why? and what wrong is it? Please give me reply as possible as you can.
> Thanks!
what's version psql you working on??
I pasted your example, it works fine.
you might miss something...
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Mon, 5 Mar 2001, Lu Raymond wrote:
> Hello,all
>
> I use your a example of PL/pgSQL, but there are some errors when I execute
> these codes. The details are followings,
>
> First, I create a exam.sql that includes these codes as followings,
>
> CREATE TABLE emp (
> empname text,
> salary int4,
> last_date datetime,
> last_user name);
>
> CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS'
> BEGIN
> -- Check that empname and salary are given
> IF NEW.empname ISNULL THEN
> RAISE EXCEPTION ''empname cannot be NULL value'';
> END IF;
> IF NEW.salary ISNULL THEN
> RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
> END IF;
>
> -- Who works for us when she must pay for?
> IF NEW.salary < 0 THEN
> RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
> END IF;
>
> -- Remember who changed the payroll when
> NEW.last_date := ''now'';
> NEW.last_user := getpgusername();
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
> FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
>
>
>
> Secondly, I execute exam.sql and the postgress can create the table emp,
> the function emp_stamp() and the trigger emp_stamp seccessfully.But when I
> insert one record to table emp, there are some errors on the screen.
> the insert statement is followings,
> INSERT INTO emp Values('','','20001220','raymond');
>
> the error of screen is:
> NOTICE: plpgsql: ERROR during compile of emp_stamp near line 1
> "RROR: parse error at or near "
>
> Why? and what wrong is it? Please give me reply as possible as you can.
> Thanks!
>
>
>
>
>
>
>
>
>
> _________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Hello everyone, in Postgresql RDBMS , how can i get informations about acces permissions on existing objects ? I alredy know that ( psql \z ) do this ; but 'w' means update OR delete ! (it's not the same thing) can someone , please, tell me if it is possible ? thanks a lot
What do you mean 'w' means update OR delete ! (it's not the same thing) ? Do you want to know if somebody only could access (select)? If he has "r", he has select access on that object. Access information certaintly includes all (select, alter, update, insert, delete). --- Banghe hicham bouzdad wrote: > Hello everyone, > > in Postgresql RDBMS , > how can i get informations about acces permissions on existing objects > ? > > I alredy know that ( psql \z ) do this ; > but 'w' means update OR delete ! (it's not the same thing) > > can someone , please, tell me if it is possible ? > > thanks a lot > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
hicham bouzdad wrote:
>Hello everyone,
>
>in Postgresql RDBMS ,
>how can i get informations about acces permissions on existing obje
>cts
>?
>
>I alredy know that ( psql \z ) do this ;
>but 'w' means update OR delete ! (it's not the same thing)
PostgreSQL access permissions are not yet as extensive as the full SQL
spec requires.
w includes UPDATE and DELETE; there is no finer-grained permission
available.
From the GRANT manpage:
r -- SELECT
w -- UPDATE/DELETE
a -- INSERT
R -- RULE
arwR -- ALL
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Go ye therefore, and teach all nations, baptizing them
in the name of the Father, and of the Son, and of the
Holy Ghost; Teaching them to observe all things
whatsoever I have commanded you; and, lo, I am with
you alway, even unto the end of the world. Amen."
Matthew 28:19,20
Hi everyone, and thanks a lot ,
> PostgreSQL access permissions are not yet as extensive as the full SQL
> spec requires.
> 'w' includes UPDATE and DELETE;
> there is no finer-grained permission available.
if i do on (PostgreSQL 7.1beta4 ) :
GRANT UPDATE ON tabl TO user;
user can do on tabl :
UPDATE, but also DELETE and INSERT !
my question is :
can i bypass this limitation ?
I believe the only thing you can do is revoke the INSERT permission (after granting UPDATE), but I don think you can revoke the DELETE permission since is the same flag as UPDATE. hicham bouzdad wrote: > Hi everyone, and thanks a lot , > > > PostgreSQL access permissions are not yet as extensive as the full SQL > > spec requires. > > > 'w' includes UPDATE and DELETE; > > there is no finer-grained permission available. > > if i do on (PostgreSQL 7.1beta4 ) : > GRANT UPDATE ON tabl TO user; > > user can do on tabl : > UPDATE, but also DELETE and INSERT ! > > my question is : > can i bypass this limitation ? > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)