Thread: Starting with pl/pgsql..
Hello all,
I have starting with PostGreSQL. I come from Oracle 8i and 9i but we want to start using PostGreSQL in a few projects
andsee if it runs fine.
I have testing now pl/pgsql language. It is very important for me because I have a lot of oracle pl/sql code in all my
databasesand I think is better do almost everything will be possible on the server than the client...
Ok. I have read all Oracle -> PostGreSQL documents (Programmer, O'reilly book, etc)... and I am trying to do my first
pl/pgsql"procedure".
First question:
ONLY functions exists on pl/pgsql ? No Procedures ? No Packages ?
Second question:
I am doing this and obtaining this too:
www=# select x(10,40);
NOTICE: Error occurred while executing PL/pgSQL function x
NOTICE: line 8 at assignment
ERROR: zero-length delimited identifier
I have changed almost everything. The way to do, variable names, etc.. but the same error (or others) I am obtained.
I don't understand anything.... and it is a TEST-EASY function. :-((((
Thanks and best regards..
=============================================================
Table "pepe"
Column | Type | Modifiers
--------+-----------------------+-----------
a | numeric(2,0) | not null
b | character varying(50) |
Primary key: pepe_pkey
=============================================================
-- Function: x(int4, int4)
CREATE FUNCTION "x"("int4", "int4") RETURNS "int4" AS ' DECLARE
inicio alias for $1;
final alias for $2;
--
texto varchar;
BEGIN
FOR X IN inicio..final LOOP
texto := "ESTE ES: " || X;
INSERT INTO pepe VALUES (X, texto);
END LOOP;
END;
' LANGUAGE 'plpgsql';
=============================================================
Ok so I don't know a thing about Oracle. If you didn't already notice them
there are multiple docs on techdocs.postgresql.org on porting from oracle.
Now your function:
> Table "pepe"
> Column | Type | Modifiers
> --------+-----------------------+-----------
> a | numeric(2,0) | not null
> b | character varying(50) |
> -- Function: x(int4, int4)
> CREATE FUNCTION "x"("int4", "int4") RETURNS "int4" AS ' DECLARE
You may want to avoid the use to double-quoting your identifiers. Without
the quotes PostgreSQL will fold the case to the default lower case and
you're restricted to single word identifiers. With quotes you can go and
create identifiers like "BiCap" or even "multiple words". I'll just define
that as a problem waiting to happen. I wouldn't go there unless you had
some real reason to force case sensitivity.
> inicio alias for $1;
> final alias for $2;
> --
> texto varchar;
> BEGIN
> FOR X IN inicio..final LOOP
> texto := "ESTE ES: " || X;
Again double quotes. Substitute single quotes instead. Use double quotes
for identifiers and single quotes for identifiers. You are already in a
single-quoted section so quote the single quote ala:
texto := ''ESTE ES: '' || X;
> INSERT INTO pepe VALUES (X, texto);
> END LOOP;
> END;
> ' LANGUAGE 'plpgsql';
Josh
Hi Josh,
> Ok so I don't know a thing about Oracle. If you didn't already notice them
> there are multiple docs on techdocs.postgresql.org on porting from oracle.
Thanks, I have read all of them (I think so)...
> Now your function:
>
> > Table "pepe"
> > Column | Type | Modifiers
> > --------+-----------------------+-----------
> > a | numeric(2,0) | not null
> > b | character varying(50) |
>
> > -- Function: x(int4, int4)
> > CREATE FUNCTION "x"("int4", "int4") RETURNS "int4" AS ' DECLARE
>
> You may want to avoid the use to double-quoting your identifiers. Without
> the quotes PostgreSQL will fold the case to the default lower case and
> you're restricted to single word identifiers. With quotes you can go and
> create identifiers like "BiCap" or even "multiple words". I'll just define
> that as a problem waiting to happen. I wouldn't go there unless you had
> some real reason to force case sensitivity.
I have never put double-quoting in any table, identifier or so. But I have copy/paste from pgadmin II and it put there
thatquotes.
> > inicio alias for $1;
> > final alias for $2;
> > --
> > texto varchar;
> > BEGIN
> > FOR X IN inicio..final LOOP
> > texto := "ESTE ES: " || X;
>
> Again double quotes. Substitute single quotes instead. Use double quotes
> for identifiers and single quotes for identifiers. You are already in a
> single-quoted section so quote the single quote ala:
>
> texto := ''ESTE ES: '' || X;
>
I have substitute my double-quotes with single quotes and here the results:
-*- It runs fine from pgadmin.
-*- It runs fine from psql (linux system).
But, I wants to test inserting more rows and then I have re-created (drop and create) table 'pepe' varying column 'a'
fromnumeric(2) to numeric(8), in this way:
=============
Table "pepe"
Column | Type | Modifiers
--------+-----------------------+-----------
a | numeric(8,0) | not null
b | character varying(50) |
Primary key: pepe_pkey
=============
Then If I call the function from 'psql' it give me this error (but with pgadmin 2 the same 'select x(10,40) runs fine):
NOTICE: Error occurred while executing PL/pgSQL function x
NOTICE: line 9 at SQL statement
ERROR: Relation 16651 does not exist
Thanks....
And that is a different issue. There is some sort of internal optimization in PG/pgSQL where objects are dereferenced and their OIDs are stored instead. This is sort of a general problem: if you delete something if other things refer to it then you may have to recreate them as well. The exception here is in plain SQL functions and in EXECUTE blocks in PL/pgSQL. In both of those cases the SQL code is parsed and executed at runtime. In other cases (I'm thinking of functions, views, triggers) the reference is symbolic 'pepe'::text until the symbolic reference is dereferenced and then it is converted to a hard reference like 16651::oid. All that means is that you drop and create your x(..,..) function after drop/create on your pepe table. If you do all the work inside of a transaction I suppose you can do all the modifications transparently to other users. This works exactly like if you had a view or other tables that use the pepe view. *same* issue. I seem to recall folks on [hackers] mentioning something about making working with dependant objects easier so this might change eventually. Joshua b. Jore ; http://www.greentechnologist.org On Sat, 6 Jul 2002, Terry Yapt wrote: > But, I wants to test inserting more rows and then I have re-created (drop and create) table 'pepe' varying column 'a' fromnumeric(2) to numeric(8), in this way: > ============= > Table "pepe" > Column | Type | Modifiers > --------+-----------------------+----------- > a | numeric(8,0) | not null > b | character varying(50) | > Primary key: pepe_pkey > ============= > > Then If I call the function from 'psql' it give me this error (but with pgadmin 2 the same 'select x(10,40) runs fine): > > NOTICE: Error occurred while executing PL/pgSQL function x > NOTICE: line 9 at SQL statement > ERROR: Relation 16651 does not exist > > Thanks.... >
Only a note: =========== Oracle do it without any problem... :-) So because of that It is something normal for me... In oracle you can looking for INVALID objects... In our example, function 'x' will be an invalid object.. Perhaps it is the solution for postgreSQL.. Thanks Josh and best regards.. Josh Jore wrote: > > You examine the oid attribute in the pg_class system table (from > [hackers], this might not be valid advice past 7.3). This assumes you > looked at the oid *before* deleting the table. This behaviour doesn't mean > you have to track which things have which oid - what you suggested. It > does mean you have to know that the function "x"(..,..) depends on the > table "pepe". You should already know your application's dependancies > to begin with. This isn't rocket science - either your developer documents > the design or you inspect the source. > > Now... it would be *nice* if the symbols could be re-resolved as needed > either as a manual or automatic process. I can dream can't I? > > Joshua b. Jore ; http://www.greentechnologist.org > > On Sat, 6 Jul 2002, Terry Yapt wrote: > > > Ok, I understand that (I imagined this behaviour also) but how can > > locate what object oid is '16561' to re-create it ? Are there any > > system table to do it ? > > > > And why 'Select x(..,..) runs in pgadmin and not in psql ??????? > > > > I think if I must to maintain an, external to pgsql, document with all > > my objects references noted then I think pgsql development team have a > > lot of work to do yet. :-( > > > > Thanks Josh, best regards.. > > > > PS: amusing self_confidence.jpg <g>.
I poked a bit further and looked broken dependancies for views, PL/pgSQL functions and triggers. It looks like PostgreSQL already handles the trigger dependancy correctly. The function relation/OID resolution is cached for the lifetime of the backend process. Once I quit and reconnected it reresolved the table name again. The issue remained for the view object. So... A function could be created to drop the view and recreate it from the saved source function. Joshua b. Jore ; http://www.greentechnologist.org On Sun, 7 Jul 2002, Terry Yapt wrote: > Only a note: > =========== > > Oracle do it without any problem... :-) So because of that It is something normal for me... > In oracle you can looking for INVALID objects... In our example, function 'x' will be an invalid object.. > > Perhaps it is the solution for postgreSQL.. > > Thanks Josh and best regards.. > > > Josh Jore wrote: > > > > You examine the oid attribute in the pg_class system table (from > > [hackers], this might not be valid advice past 7.3). This assumes you > > looked at the oid *before* deleting the table. This behaviour doesn't mean > > you have to track which things have which oid - what you suggested. It > > does mean you have to know that the function "x"(..,..) depends on the > > table "pepe". You should already know your application's dependancies > > to begin with. This isn't rocket science - either your developer documents > > the design or you inspect the source. > > > > Now... it would be *nice* if the symbols could be re-resolved as needed > > either as a manual or automatic process. I can dream can't I? > > > > Joshua b. Jore ; http://www.greentechnologist.org > > > > On Sat, 6 Jul 2002, Terry Yapt wrote: > > > > > Ok, I understand that (I imagined this behaviour also) but how can > > > locate what object oid is '16561' to re-create it ? Are there any > > > system table to do it ? > > > > > > And why 'Select x(..,..) runs in pgadmin and not in psql ??????? > > > > > > I think if I must to maintain an, external to pgsql, document with all > > > my objects references noted then I think pgsql development team have a > > > lot of work to do yet. :-( > > > > > > Thanks Josh, best regards.. > > > > > > PS: amusing self_confidence.jpg <g>. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > >