[GENERAL] porting vb6 code to pgplsql, referencing fields
Hi everyone,
I'm trying to port some vb6 code to pgplsql (PostgreSQL 8.3 winxp)
that code is
Const f2_MAX_TRAMS = 5
Dim f2_rTarifaA as new ADODB.Recordset
Dim Mpa(f2_MAX_TRAMS) As Double ' preu aigua
Dim Ma(f2_MAX_TRAMS) As Long' m3 aigua tarifa
Dim i As Integer, j As Integer ' indexs matrius
...
( open connection, open recordset, etc )
...
-- fill array with field values of M3TRAM1, ..., M3TRAM5
-- PREU1, ..., PREU5
for i = 1 to f2_MAX_TRAMS
Ma(i) = f2_rTarifaA.Fields("M3TRAM" + CStr(i)).Value
Mpa(i) = f2_rTarifaA.Fields("PREU" + CStr(i)).Value
next
in pgplsql, more or less
DECLARE
c_tarifa_c CURSOR (dfac DATE, key INTEGER) IS SELECT * FROM F2_TARIFA_C
WHERE TIPUS = key AND dfac BETWEEN DINICI AND DFINAL;
f2_MAX_TRAMS CONSTANT INTEGER := 5;
Ma INTEGER[5];
Mpa NUMERIC(10,2)[5];
row_tfa f2_tarifa_a%rowtype;
BEGIN
OPEN c_tarifa_a (datafac, f2_Mtar);
FETCH c_tarifa_a INTO row_tfa;
CLOSE c_tarifa_a;
For i IN 1..f2_MAX_TRAMS LOOP
Ma[i] := row_tfa. ? -- "M3TRAM" + CStr(i)).Value
Mpa[i] := row_tfa. ? -- "PREU" + CStr(i)).Value
END LOOP;
END
I would like to know some tips about:
How can I declare arrays especifying the size with a constant,
but the most important is how can I reference the fields inside de loop
Thanks in advance
Josep
[GENERAL] porting vb6 code to pgplsql, referencing fields
Hi everyone,
I'm trying to port some vb6 code to pgplsql (PostgreSQL 8.3 winxp)
that code is
Const f2_MAX_TRAMS = 5
Dim f2_rTarifaA as new ADODB.Recordset
Dim Mpa(f2_MAX_TRAMS) As Double ' preu aigua
Dim Ma(f2_MAX_TRAMS) As Long' m3 aigua tarifa
Dim i As Integer, j As Integer ' indexs matrius
...
( open connection, open recordset, etc )
...
-- fill array with field values of M3TRAM1, ..., M3TRAM5
-- PREU1, ..., PREU5
for i = 1 to f2_MAX_TRAMS
Ma(i) = f2_rTarifaA.Fields("M3TRAM" + CStr(i)).Value
Mpa(i) = f2_rTarifaA.Fields("PREU" + CStr(i)).Value
next
in pgplsql, more or less
DECLARE
c_tarifa_c CURSOR (dfac DATE, key INTEGER) IS SELECT * FROM F2_TARIFA_C
WHERE TIPUS = key AND dfac BETWEEN DINICI AND DFINAL;
f2_MAX_TRAMS CONSTANT INTEGER := 5;
Ma INTEGER[5];
Mpa NUMERIC(10,2)[5];
row_tfa f2_tarifa_a%rowtype;
BEGIN
OPEN c_tarifa_a (datafac, f2_Mtar);
FETCH c_tarifa_a INTO row_tfa;
CLOSE c_tarifa_a;
For i IN 1..f2_MAX_TRAMS LOOP
Ma[i] := row_tfa. ? -- "M3TRAM" + CStr(i)).Value
Mpa[i] := row_tfa. ? -- "PREU" + CStr(i)).Value
END LOOP;
END
I would like to know some tips about:
How can I declare arrays especifying the size with a constant,
but the most important is how can I reference the fields inside de loop
Thanks in advance
Josep
[GENERAL] porting vb6 code to pgplsql, referencing fields
Hi everyone,
I'm trying to port some vb6 code to pgplsql (PostgreSQL 8.3 winxp)
that code is
Const f2_MAX_TRAMS = 5
Dim f2_rTarifaA as new ADODB.Recordset
Dim Mpa(f2_MAX_TRAMS) As Double ' preu aigua
Dim Ma(f2_MAX_TRAMS) As Long' m3 aigua tarifa
Dim i As Integer, j As Integer ' indexs matrius
...
( open connection, open recordset, etc )
...
-- fill array with field values of M3TRAM1, ..., M3TRAM5
-- PREU1, ..., PREU5
for i = 1 to f2_MAX_TRAMS
Ma(i) = f2_rTarifaA.Fields("M3TRAM" + CStr(i)).Value
Mpa(i) = f2_rTarifaA.Fields("PREU" + CStr(i)).Value
next
in pgplsql, more or less
DECLARE
c_tarifa_c CURSOR (dfac DATE, key INTEGER) IS SELECT * FROM F2_TARIFA_C
WHERE TIPUS = key AND dfac BETWEEN DINICI AND DFINAL;
f2_MAX_TRAMS CONSTANT INTEGER := 5;
Ma INTEGER[5];
Mpa NUMERIC(10,2)[5];
row_tfa f2_tarifa_a%rowtype;
BEGIN
OPEN c_tarifa_a (datafac, f2_Mtar);
FETCH c_tarifa_a INTO row_tfa;
CLOSE c_tarifa_a;
For i IN 1..f2_MAX_TRAMS LOOP
Ma[i] := row_tfa. ? -- "M3TRAM" + CStr(i)).Value
Mpa[i] := row_tfa. ? -- "PREU" + CStr(i)).Value
END LOOP;
END
I would like to know some tips about:
How can I declare arrays especifying the size with a constant,
but the most important is how can I reference the fields inside de loop
Thanks in advance
Josep
Re: [GENERAL] porting vb6 code to pgplsql, referencing fields
thanks Craig your assumption is right. I have a given table structure, so redesign it now is not possible due to having change a lot of things Furthermore, using M3TRAM INTEGER[5], PREU NUMERIC(10,2)[5] seems to me a very good way but I think it may appear problems when accessing to that table from third party apps such as excel, odbc, ... isn't it? So the simplest way could be the most suitable one. However, imagine I had more fields Is not really possible to 'calculate' a string, that is the field name, yeah like it was an array, and reference a field in a row using that string? something like this s:='PREU1'; row_tfa.s := x; anyway, I a completely newbie in pgplsql, and I see my way of thinking is not pgplsql thanks Josep Porres 2008/3/13, Craig Ringer <[EMAIL PROTECTED]>: > > josep porres wrote: > > > but the most important is how can I reference the fields inside de loop > > > By "the fields" I assume you mean the fields with names that end in a > number from 1 to 5, and you want to access those fields in a loop as if > you were indexing an array? > > I think you might want to explain what you're actually trying to do, as > the right answer might not really be how to load/store your array but > might involve looking at how and why you're using arrays this way too. > > In particular, maybe it's better to store an array in the record. > > > > > Looking at your VB6 code it appears that your f2_tarifa_a table has some > sequentially numbered fields, and might be defined like (assuming a > SERIAL pkey): > > CREATE TABLE f2_tarifa_a ( > id SERIAL PRIMARY KEY, > -- other values > M3TRAM1 INTEGER, > PREU1 NUMERIC(10,2) > M3TRAM2 INTEGER, > PREU2 NUMERIC(10,2) > M3TRAM3 INTEGER, > PREU3 NUMERIC(10,2) > M3TRAM4 INTEGER, > PREU4 NUMERIC(10,2) > M3TRAM5 INTEGER, > PREU5 NUMERIC(10,2) > ); > > ... and you're essentially using it to store 5-element arrays. You have > a few options here. The simplest is probably just to explicitly fetch > each element of the array, eg: > > Ma[1] := row_tfa.M3TRAM1; > Mpa[1] := row_tfa.PREU1; > Ma[2] := row_tfa.M3TRAM2; > Mpa[2] := row_tfa.PREU2; > > etc. > > Alternately you could adjust your schema to store arrays: > > > CREATE TABLE f2_tarifa_a ( > id SERIAL PRIMARY KEY, > -- other values > M3TRAM INTEGER[5], > PREU NUMERIC(10,2)[5] > ); > > ... and fetch/store those directly. > > Another option is to switch from using an array to a secondary table. If > your arrays are in any way variable in length that's probably a good > ideea. For example: > > > CREATE TABLE f2_tarifa_a ( > id SERIAL PRIMARY KEY, > -- other values > ); > > CREATE TABLE f2_tarifa_a_trampreu ( > f2_tarifa_a_id INTEGER REFERENCES f2_tarifa_a(id) ON DELETE CASCADE, > M3TRAM INTEGER, > PREU NUMERIC(10,2) > ); > CREATE INDEX f2_tarifa_a_trampreu_fkey_id > ON f2_tarifa_a_trampreu(f2_tarifa_a_id); > > ... then you can FOR loop though the secondary table. >
[GENERAL] pgplsql, how to save row variable to a table row
Hi, i'm learning how to work with rows (rowtype) , but I cannot find a way to insert one, once i filled up the fields, in a table. the thing is: I have some rows filled up with execute commands as you can see below. Once I've filled the row_tempf, how can I insert this row to the table f2_tempfac? Can I use the execute command for that? Thanks! Josep Porres DECLARE row_tfa f2_tarifa_a%rowtype; row_tempff2_tempfac%rowtype; ... BEGIN ... BEGIN EXECUTE 'SELECT * FROM F2_TARIFA_A WHERE (TIPUS = ' || quote_literal(f2_Mtar) || ') AND ' || quote_literal(datafac) || ' BETWEEN DINICI AND DFINAL' INTO STRICT row_tfa; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'TARIFA_A % no trobada.', f2_Mtar; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'TARIFA_A % no unica.', f2_Mtar; END; ... row_tempf.field1 := value1; row_tempf.field2 := value3; ... row_tempf.fieldN := valueN; -- NOW INSERT row_tempf in the associated table -- ??? END;
Re: [GENERAL] pgplsql, how to save row variable to a table row
I've tried: EXECUTE 'INSERT INTO F2_TEMPFAC SELECT * FROM row_tempf'; but I've got not surprisingly: ERROR: relation "row_tempf" does not exist Estado SQL:42P01 Contexto:SQL statement "INSERT INTO F2_TEMPFAC SELECT * FROM row_tempf" PL/pgSQL function "f2_facturar" line 437 at EXECUTE statement I'm going to try your suggestion, however it's a bit annoying because this table has a lot of fields. maybe i'm lazy to write all of them :P ... well... hands on it!!! thx Josep 2008/3/26, Raymond O'Donnell <[EMAIL PROTECTED]>: > > On 26/03/2008 11:59, josep porres wrote: > > > row_tempf.field1 := value1; > > row_tempf.field2 := value3; > > ... > > row_tempf.fieldN := valueN; > > > > -- NOW INSERT row_tempf in the associated table > > -- ??? > > > Easy! - > > insert into ( ... ) >values (row_tempf.field1, row_tempf.field2, ... ); > > :-) > > Ray. > > --- > > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > [EMAIL PROTECTED] > --- >
Re: [GENERAL] pgplsql, how to save row variable to a table row
INSERT INTO demo_tab SELECT demo_tab_row.*; I tried this before with no success. But I realized that it was because a problem with a constraint, no warnings about this at all. With the detailed insert i've got the constraint error. I was using the execute because I had a variable called the same as a field, not good, I changed it. So now it's working perfectly!!! Thanks a lot guys! Josep Porres
[GENERAL] PL/pgSQL Documentation, biblio, etc
Hi guys, Is there any other online place, apart from http://www.postgresql.org/docs/8.3/static/plpgsql.html , to get a reference or a wider explanation of PL/pgSQL ? Do you recommend any book? thx Josep Porres
Re: [GENERAL] SELECT DISTINCT ON and ORDER BY
maybe this? select value, max(id) as id, max(order_field) as order_field from mytable group by value order by 3 2008/3/28, Stanislav Raskin <[EMAIL PROTECTED]>: > > Hello everybody, > > > > I have a table like this one: > > > > id value order_field > > 1 103 > > 2 124 > > 3 101 > > 4 5 8 > > 5 122 > > > > What I want to do, is to do something like > > > > SLECT DISTINCT ON (my_table.value) > > my_table.id, my_table.value, my_table.order_field > > FROM my_table > > ORDER BY order_field > > > > Hence selecting rows with distinct values, but primarily ordered by > order_field, instead of value, which is requires by DISTINCT ON. > > The result in this case should be: > > > > id value order_field > > 3 101 > > 5 122 > > 4 5 8 > > > > How do I do this? I do need order_field in the select list to use it in > the ORDER statement, which is why – as far as I can see – GROUP BY and > SELECT DISTINCT are useless. Did I miss out on something? > > > > Thank you in advance >
Re: [GENERAL] PostgreSQL and Java on WindowsXP
maybe you don't need PL/Java... You are trying to connect using JDBC, right? Take a look at http://jdbc.postgresql.org/doc.html Which errors do you get? Is the server listening on standard port? anyway, you have to provide more information. Josep 2008/3/30, Vismaster46 <[EMAIL PROTECTED]>: > > Hello! > > I need to install on my local Windows XP machine PostgreSQL to test > some Java applications. > > The trouble is that I cannot connect to PostgreSQL via Java > application, because the connection fails every time...what's the > problem? I have not installed PL/Java package cause my Postgre > installer disabled this option...it is the matter??? > > Thank you in advance. > > Bye! > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] minimum and maximum functions
hh ok greatest()/least() 2008/4/1, josep porres <[EMAIL PROTECTED]>: > > I cannot find predefined functions to find minimum and maximum of at least > two values. > Aren't there any? > > select min(3,6) > 3 > > select max(3,6) > 6 > > > > Josep Porres >
[GENERAL] minimum and maximum functions
I cannot find predefined functions to find minimum and maximum of at least two values. Aren't there any? select min(3,6) 3 select max(3,6) 6 Josep Porres
[GENERAL] edb-debugger, debugging pl/pgsql
Hi, I don't know if here is the right place to post this, but anyway ... Does anybody know if there is any binary dist for win32 edb-debugger for pgsql8.3? If not, do you know which compiler I have to use? I don't need to install anything on the client side where is my pgadmin, right? thx Josep Porres
Re: [GENERAL] edb-debugger, debugging pl/pgsql
ok, thx, I can debug from server pgadmin which ships with pg8.3 (1.8.2 rev 7030), because it runs on windows, but in the client side, running pgadmin1.8.2 rev 7050 it seems to hang, it freezes. Do I need to config anything? thx Josep Porres 2008/4/7, Dave Page <[EMAIL PROTECTED]>: > > On Mon, Apr 7, 2008 at 8:11 AM, josep porres <[EMAIL PROTECTED]> wrote: > > > > Hi, > > > > I don't know if here is the right place to post this, but anyway ... > > Does anybody know if there is any binary dist for win32 edb-debugger for > > pgsql8.3? > > If not, do you know which compiler I have to use? > > I don't need to install anything on the client side where is my > pgadmin, > > right? > > > > > It ships with the binary distro of PG 8.3 for Windows. > > And, no, you don't need anything except pgAdmin 1.8+ on the client side. > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] edb-debugger, debugging pl/pgsql
I downloaded it from: http://www.postgresql.org/ftp/pgadmin3/release/v1.8.2/win32/ spaninsh http mirror http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fpgadmin3%2Frelease%2Fv1.8.2%2Fwin32%2Fpgadmin3-1.8.2-2.zip Is this version wrong? Josep Porres 2008/4/7, Dave Page <[EMAIL PROTECTED]>: > > On Mon, Apr 7, 2008 at 9:29 AM, josep porres <[EMAIL PROTECTED]> wrote: > > ok, thx, > > I can debug from server pgadmin which ships with pg8.3 (1.8.2 rev 7030), > > because it runs on windows, > > but in the client side, running pgadmin1.8.2 rev 7050 it seems to hang, > it > > freezes. > > Do I need to config anything? > > > No, but where did you get rev 7050 from? That's quite a few revisions > after 1.8.2 was tagged. > > > -- > > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] edb-debugger, debugging pl/pgsql
well, when you asked me about where I downloaded it, I downloaded it from I installed it again. It seems I can debug with no problems till now. However, when I begin to debug and the function parameters window appears, if I click cancel it freezes. the log says: 2008-04-07 11:05:05 STATUS : Obteniendo Función detalles... 2008-04-07 11:05:05 STATUS : Obteniendo Función detalles... (0,01 seg) 2008-04-07 11:05:05 QUERY : Scalar query (192.168.1.3:5432): SELECT count(*) FROM pg_proc WHERE proname = 'pldbg_get_target_info'; 2008-04-07 11:05:05 QUERY : Query result: 1 2008-04-07 11:05:05 QUERY : Scalar query (192.168.1.3:5432): SELECT count(*) FROM pg_proc WHERE proname = 'plpgsql_oid_debug'; 2008-04-07 11:05:05 QUERY : Query result: 1 2008-04-07 11:05:08 QUERY : Scalar query (192.168.1.3:5432): SELECT count(*) FROM pg_proc WHERE oid = 16439 2008-04-07 11:05:08 QUERY : Query result: 1 2008-04-07 11:05:08 QUERY : SET log_min_messages TO fatal 2008-04-07 11:05:08 QUERY : select t.*, pg_catalog.oidvectortypes( t.argtypes ) as argtypenames, t.argtypes as argtypeoids, l.lanname, n.nspname, p.proretset, y.typname AS rettype from pldbg_get_target_info( '16439', 'o' ) t , pg_namespace n, pg_language l, pg_proc p, pg_type y where n.oid = t.schema and l.oid = t.targetlang and p.oid = t.target and y.oid = t.returntype 2008-04-07 11:05:08 QUERY : SELECT version(); thanks dave Josep Porres 2008/4/7, Dave Page <[EMAIL PROTECTED]>: > > On Mon, Apr 7, 2008 at 9:40 AM, josep porres <[EMAIL PROTECTED]> wrote: > > I downloaded it from: > > http://www.postgresql.org/ftp/pgadmin3/release/v1.8.2/win32/ > > spaninsh http mirror > > > http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fpgadmin3%2Frelease%2Fv1.8.2%2Fwin32%2Fpgadmin3-1.8.2-2.zip > > > > Is this version wrong? > > > No, I forgot it got re-rolled. > > Can you supply a debug log showing pgAdmin hanging after you start > debugging? Look under File -> Options -> Logging. > > > > > -- > > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] edb-debugger, debugging pl/pgsql
if I try to close the parameter window using X button instead of Cancel, it's the same. It's a bit annoying when I finnish debugging, but I can debug, so it's fine :) I will be looking forward new versions. thanks again Dave Josep Porres 2008/4/7, Dave Page <[EMAIL PROTECTED]>: > > On Mon, Apr 7, 2008 at 10:21 AM, josep porres <[EMAIL PROTECTED]> wrote: > > well, when you asked me about where I downloaded it, I downloaded it > from I > > installed it again. > > It seems I can debug with no problems till now. > > However, when I begin to debug and the function parameters window > appears, > > if I click cancel it freezes. > > > Yeah, there is a known issue when doing that. There is a patch being > tested at the moment. > > > > -- > > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] edb-debugger, debugging pl/pgsql
thanks Tony, I'm going to take a look to it tomorrow morning. Josep Porres 2008/4/7, Tony Caduto <[EMAIL PROTECTED]>: > > josep porres wrote: > > > > > 2008/4/7, Dave Page <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>: > > > >On Mon, Apr 7, 2008 at 10:21 AM, josep porres <[EMAIL PROTECTED] > ><mailto:[EMAIL PROTECTED]>> wrote: > >> well, when you asked me about where I downloaded it, I > >downloaded it from I > >> installed it again. > >> It seems I can debug with no problems till now. > >> However, when I begin to debug and the function parameters > >window appears, > >> if I click cancel it freezes. > > > > > > > > Josep, > > The stand alone Lightning Debugger does not have this issue and if it does > crash or hang (not likely) it won't take down whatever admin tool you are > using. Oh, and it's FREE. > > http://www.amsoftwaredesign.com/debugger_client_announce > > Check it out works great on win32 and built with a native compiler with a > high performance memory manager. > > > Tony Caduto > AM Software > http://www.amsoftwaredesign.com >
[GENERAL] wrong query result
Hi I just want to let you know one thing. By the way, I'm running Postgres 8.3.1 on windows I was making a query using pgadmin: select clau, lecact-lecant as m3, m3any, ta, tc, 3 as mesos, persones, true as clavegueram from fac_abonats where clau in (select clau from tmp_claus_prova) order by clau clau is the primary key of the table fac_abonats the thing is that the result of the query returns the same number of rows as the table fac_abonats (thousands). select clau from tmp_claus_prova return about one hundred rows, and all the values in the column exists in fac_abonats. So maybe something wrong is happening in the where clause. At the end I've realized that there is no column named 'clau'. It has another name. So the subselect query is wrong, if I execute it alone, an error appears ERROR: no existe la columna «clau» LINE 6: select clau from tmp_claus_prova ^ ** Error ** ERROR: no existe la columna «clau» Estado SQL:42703 Caracter: 188 The question is: Is it a normal behaviour? Because I think that if the query is wrong, wouldn't be better to raise an error? Regards, Josep Porres
Re: [GENERAL] reproducible database crash with simple sql command on postgres 8.3.1
No problems here. create table regions (id integer, name varchar); alter table regions alter column name set default 'bavaria'; PostgreSQL 8.3.1 vc++ build 1400 xp sp2 # - Memory - shared_buffers = 32MB# min 128kB or max_connections*16kB # (change requires restart) #temp_buffers = 8MB# min 800kB temp_buffers = 8MB# min 800kB #max_prepared_transactions = 5# can be 0 or more # (change requires restart) # Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). #work_mem = 1MB# min 64kB work_mem = 2MB# min 64kB #maintenance_work_mem = 16MB# min 1MB maintenance_work_mem = 32MB# min 1MB max_stack_depth = 2MB# min 100kB cheers Josep 2008/5/9 <[EMAIL PROTECTED]>: > > hi all, > > first i create a table > create table regions (id integer, name varchar); > > then i want to set a default value for a column, e.g. > alter table regions alter column name set default 'bavaria'; > > at this point crashes the database with the message > > PANIK: ERROR_STACK_SIZE exceeded > > the rest of the message is unfortunately in german then i have to > restart the postgres-service manually this error is very easy > reproducible at my environment > > is this a problem of my installation or can anybody reproduce this error > at his environment > > thanks and greetings from munich > christian > > p.s.os windows xp, sp2 >postgres 8.3.1, visual c++, build 1400 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] db error messages when I try to debug with pgadmin
Hi guys, I cannot debug with pgadmin 1.10.2, I get an error in both debugging options. I have de debugging plugin loaded in a 8.4.3 server These are the errors I get DEBUG CONTEXT MENU OPTION I set the parameter values and hit OK. ERROR: function plpgsql_oid_debug(integer, integer) does not exist LINE 1: select plpgsql_oid_debug( 0, 17457 ); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SET BREAKPOINT CONTEXT MENU OPTION ERROR: syntax error at or near "," LINE 1: SELECT * FROM pldbg_set_global_breakpoint(, 17457, NULL, NUL... ^ Subwindow: row number 0 is out of range 0..-1 The function plpgsql_oid_debug which I have in the db is: CREATE OR REPLACE FUNCTION plpgsql_oid_debug(functionoid oid) RETURNS integer AS '$libdir/plugins/plugin_debugger', 'plpgsql_oid_debug' LANGUAGE 'c' VOLATILE STRICT COST 1; ALTER FUNCTION plpgsql_oid_debug(oid) OWNER TO postgres; Do I have to exec any script to have the correct functions or something like that? What's going wrong? Could you give me any tip? Thanks Josep Porres
Re: [GENERAL] db error messages when I try to debug with pgadmin
I solved this issue creating the functions again using the script pldbgapi.sql in http://pgfoundry.org/frs/download.php/1916/edb-debugger-0.93.tgz thanks for your support josep 2010/3/17 josep porres > > Hi guys, > > > I cannot debug with pgadmin 1.10.2, I get an error in both debugging > options. > I have de debugging plugin loaded in a 8.4.3 server > > > These are the errors I get > > DEBUG CONTEXT MENU OPTION > > I set the parameter values and hit OK. > > ERROR: function plpgsql_oid_debug(integer, integer) does not exist > LINE 1: select plpgsql_oid_debug( 0, 17457 ); > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. > > > SET BREAKPOINT CONTEXT MENU OPTION > > ERROR: syntax error at or near "," > LINE 1: SELECT * FROM pldbg_set_global_breakpoint(, 17457, NULL, NUL... > ^ > Subwindow: > row number 0 is out of range 0..-1 > > > > The function plpgsql_oid_debug which I have in the db is: > > CREATE OR REPLACE FUNCTION plpgsql_oid_debug(functionoid oid) > RETURNS integer AS > '$libdir/plugins/plugin_debugger', 'plpgsql_oid_debug' > LANGUAGE 'c' VOLATILE STRICT > COST 1; > ALTER FUNCTION plpgsql_oid_debug(oid) OWNER TO postgres; > > > > Do I have to exec any script to have the correct functions or something > like that? > What's going wrong? Could you give me any tip? > > Thanks > > Josep Porres >
[GENERAL] debugging in pgadmin
Hi all A lot of time since the last debugging activity. I don't remember how to debug. I thought I had to set a breaking point in the function i want to debug, and then call that function. I'm doing this, and from another query window, i call the function. But it returns me the result, but it doesn't stop inside the function in order to debug it. What am i doing wrong? thx Josep
Re: [GENERAL] debugging in pgadmin
mmm... my database schema have the pldbg functions. 2009/3/23 Glyn Astill > > > --- On Mon, 23/3/09, josep porres wrote: > > > A lot of time since the last debugging activity. > > I don't remember how to debug. I thought I had to set a > > breaking point in > > the function i want to debug, > > and then call that function. > > I'm doing this, and from another query window, i call > > the function. But it > > returns me the result, but it doesn't stop inside the > > function in order to > > debug it. > > What am i doing wrong? > > Perhaps you've not got the pldebugger contrib module installed and run the > sql script to create the debug functions? > > > >
Re: [GENERAL] debugging in pgadmin
yes, i have: shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'# (change requires restart) 2009/3/23 Dave Page > On Mon, Mar 23, 2009 at 12:40 PM, josep porres wrote: > > mmm... my database schema have the pldbg functions. > > Do you have something like this in your postgresql.conf: > > shared_preload_libraries = '$libdir/plugins/plugin_debugger.so' > > ? > > If not, add it, and restart the server (if you're on Windows, use > plugin_debugger.dll) > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] debugging in pgadmin
it is Postgres Plus 8.3 Postgres 8.3.4 build 1400 2009/3/23 josep porres > yes, i have: > shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'# > (change requires restart) > > > 2009/3/23 Dave Page > > On Mon, Mar 23, 2009 at 12:40 PM, josep porres wrote: >> > mmm... my database schema have the pldbg functions. >> >> Do you have something like this in your postgresql.conf: >> >> shared_preload_libraries = '$libdir/plugins/plugin_debugger.so' >> >> ? >> >> If not, add it, and restart the server (if you're on Windows, use >> plugin_debugger.dll) >> >> -- >> Dave Page >> EnterpriseDB UK: http://www.enterprisedb.com >> > >
Re: [GENERAL] debugging in pgadmin
"serverversionstr","serverversionnum","proxyapiver","serverprocessid" "PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)",80300,3,4220 2009/3/23 Dave Page > On Mon, Mar 23, 2009 at 12:50 PM, josep porres wrote: > > yes, i have: > > shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'# > > (change requires restart) > > Whats the output from: > > select * from pldbg_get_proxy_info(); > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] debugging in pgadmin
in the previous action, first of all I made a breakpoint and called the function from a query window. nothing happened, the only thing i got: the result. All actions are in the previous log. Is it what you want? 2009/3/23 Dave Page > On Mon, Mar 23, 2009 at 1:41 PM, josep porres wrote: > > that way I can debug =) > > OK, so in that case can I get a log of an attempt to set a global > breakpoint please? > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] debugging in pgadmin
good morning, I have a demo database, but it's empty. if you tell me where is the creation script, I will try it 2009/3/23 Dave Page > On Mon, Mar 23, 2009 at 1:56 PM, josep porres wrote: > > well, now a log with only trying to debug setting a breakpoint > > Hmmm - do you still have the demo schema on that server? Can you try > setting a breakpoint on the list_emp() function, and then calling it > please? > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] debugging in pgadmin
i've just upgraded the server to 8.3.7 holding the same data directory select * from pldbg_get_proxy_info(); "serverversionstr","serverversionnum","proxyapiver","serverprocessid" "PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)",80300,3,2948 is it ok for testing your dll's? 2009/3/24 Dave Page > On Tue, Mar 24, 2009 at 7:50 AM, josep porres wrote: > > Ooops, I haven't realized the second schema, I've found the function. > > Here you are your test log. > > OK, I think we've tracked down the problem. Can I send you some > updated DLLs to try offlist? > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
Re: [GENERAL] debugging in pgadmin
by the way, after upgrading, i get the same behaviour even executing the function in a query window created after setting the breakpoint. 2009/3/24 josep porres > i've just upgraded the server to 8.3.7 holding the same data directory > > select * from pldbg_get_proxy_info(); > "serverversionstr","serverversionnum","proxyapiver","serverprocessid" > "PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) > 3.4.2 (mingw-special)",80300,3,2948 > > is it ok for testing your dll's? > > 2009/3/24 Dave Page > > On Tue, Mar 24, 2009 at 7:50 AM, josep porres wrote: >> > Ooops, I haven't realized the second schema, I've found the function. >> > Here you are your test log. >> >> OK, I think we've tracked down the problem. Can I send you some >> updated DLLs to try offlist? >> >> >> -- >> Dave Page >> EnterpriseDB UK: http://www.enterprisedb.com >> > >
Re: [GENERAL] update error
ok, it's working without the alias inside the SET thanks 2009/4/24 Filip Rembiałkowski > > > 2009/4/24 josep porres > >> Hello, >> >> I am migrating a database to pg and modifying the app. >> In that process I found an error and i don't see the reason. >> >> It's happening when executing the next update sentence >> Do you see what's wrong? > > > table name / alias is not allowed in SET section. > > http://www.postgresql.org/docs/current/static/sql-update.html > > > Do not include the table's name in the specification of a target column — > for example, UPDATE tab SET tab.col = 1 is invalid. > > >> >> >> The server is pg8.3.7 for w32 >> >> >> update talls as t >> set t.estat=5, t.d_pag=now(), t.ts_update=now() >> where >> t.estat in (0,1) and t.data = '02/04/2009' >> and t.clau not in ( select p.clau from pendents p >> where (t.data - p.data) >= 90 ) >> >> ERROR: column "t" of relation "talls" does not exist >> LINE 3: set t.estat=5, t.d_pag=now(), t.ts_update=now() >> ^ >> >> ** Error ** >> >> ERROR: column "t" of relation "talls" does not exist >> estat SQL: 42703 >> Caràcter: 25 >> _ >> >> the table definitions are these: >> >> CREATE TABLE TALLS ( >> DATADATE NOT NULL, >> CLAUVARCHAR(12) NOT NULL, >> CLAU_ABOVARCHAR(12), >> LLIBRE INTEGER, >> FULLA NUMERIC(10) DEFAULT 0, >> NOM VARCHAR(40), >> ADRECA VARCHAR(40), >> NUMERCOMPT VARCHAR(15), >> LECACT NUMERIC(10) DEFAULT 0, >> LECTURA NUMERIC(10) DEFAULT 0, >> REFORMA NUMERIC(1) DEFAULT 0, >> MOTIU NUMERIC(1) DEFAULT 0, >> NOTES VARCHAR(100), >> D_TALL DATE, >> D_PAG DATE, >> D_RECON DATE, >> TS_UPDATE TIMESTAMP, >> ESTAT NUMERIC(2) DEFAULT 0, >> CPARAT NUMERIC(1) DEFAULT 0, >> NOUCOMPTVARCHAR(15), >> REINCID NUMERIC(1) DEFAULT 0, >> CPOSTAL NUMERIC(5), >> CONSTRAINT PK_TALLS PRIMARY KEY (DATA, CLAU)); >> >> CREATE TABLE PENDENTS ( >> CLAU VARCHAR(12), >> DATA DATE, >> TITULAR VARCHAR(40), >> BANC VARCHAR(4), >> SUCURSAL VARCHAR(4), >> COMPTEVARCHAR(10), >> IMPORTNUMERIC(10,2), >> L1VARCHAR(40) DEFAULT '', >> L2VARCHAR(40) DEFAULT '', >> L3VARCHAR(40) DEFAULT '', >> L4VARCHAR(40) DEFAULT '', >> L5VARCHAR(40) DEFAULT '', >> L6VARCHAR(40) DEFAULT '', >> L7VARCHAR(40) DEFAULT '', >> L8VARCHAR(40) DEFAULT '', >> L9VARCHAR(40) DEFAULT '', >> L10 VARCHAR(40) DEFAULT '', >> L11 VARCHAR(40) DEFAULT '', >> L12 VARCHAR(40) DEFAULT '', >> L13 VARCHAR(40) DEFAULT '', >> L14 VARCHAR(40) DEFAULT '', >> L15 VARCHAR(40) DEFAULT '', >> L16 VARCHAR(40) DEFAULT '', >> NOM VARCHAR(40), >> AIGUA NUMERIC(10,2) DEFAULT 0, >> QSNUMERIC(10,2) DEFAULT 0, >> LLOGUER NUMERIC(10,2) DEFAULT 0, >> CANON NUMERIC(10,2) DEFAULT 0, >> MATERIALS NUMERIC(10,2) DEFAULT 0, >> IVA7 NUMERIC(10,2) DEFAULT 0, >> DESPESES NUMERIC(10,2) DEFAULT 0, >> FIANCA NUMERIC(10,2) DEFAULT 0, >> DRET NUMERIC(10,2) DEFAULT 0, >> DATACOBRAMENTDATE, >> CLAVEGUERAM NUMERIC(10,2) DEFAULT 0, >> QSCLAVEGUERAMNUMERIC(10,2) DEFAULT 0, >> DOCUMENT VARCHAR(10), >> DEVOLUCIONUMERIC(1)DEFAULT 0, >> ESTATVARCHAR(1), >> CARTANUMERIC(1)DEFAULT 0, >> ESPECIAL NUMERIC(1)DEFAULT 0, >> CONSTRAINT PK_PENDENTS PRIMARY KEY (DOCUMENT)); >> >> > > > -- > Filip Rembiałkowski > JID,mailto:filip.rembialkow...@gmail.com > http://filip.rembialkowski.net/ >
[GENERAL] update error
Hello, I am migrating a database to pg and modifying the app. In that process I found an error and i don't see the reason. It's happening when executing the next update sentence Do you see what's wrong? The server is pg8.3.7 for w32 update talls as t set t.estat=5, t.d_pag=now(), t.ts_update=now() where t.estat in (0,1) and t.data = '02/04/2009' and t.clau not in ( select p.clau from pendents p where (t.data - p.data) >= 90 ) ERROR: column "t" of relation "talls" does not exist LINE 3: set t.estat=5, t.d_pag=now(), t.ts_update=now() ^ ** Error ** ERROR: column "t" of relation "talls" does not exist estat SQL: 42703 Caràcter: 25 _ the table definitions are these: CREATE TABLE TALLS ( DATADATE NOT NULL, CLAUVARCHAR(12) NOT NULL, CLAU_ABOVARCHAR(12), LLIBRE INTEGER, FULLA NUMERIC(10) DEFAULT 0, NOM VARCHAR(40), ADRECA VARCHAR(40), NUMERCOMPT VARCHAR(15), LECACT NUMERIC(10) DEFAULT 0, LECTURA NUMERIC(10) DEFAULT 0, REFORMA NUMERIC(1) DEFAULT 0, MOTIU NUMERIC(1) DEFAULT 0, NOTES VARCHAR(100), D_TALL DATE, D_PAG DATE, D_RECON DATE, TS_UPDATE TIMESTAMP, ESTAT NUMERIC(2) DEFAULT 0, CPARAT NUMERIC(1) DEFAULT 0, NOUCOMPTVARCHAR(15), REINCID NUMERIC(1) DEFAULT 0, CPOSTAL NUMERIC(5), CONSTRAINT PK_TALLS PRIMARY KEY (DATA, CLAU)); CREATE TABLE PENDENTS ( CLAU VARCHAR(12), DATA DATE, TITULAR VARCHAR(40), BANC VARCHAR(4), SUCURSAL VARCHAR(4), COMPTEVARCHAR(10), IMPORTNUMERIC(10,2), L1VARCHAR(40) DEFAULT '', L2VARCHAR(40) DEFAULT '', L3VARCHAR(40) DEFAULT '', L4VARCHAR(40) DEFAULT '', L5VARCHAR(40) DEFAULT '', L6VARCHAR(40) DEFAULT '', L7VARCHAR(40) DEFAULT '', L8VARCHAR(40) DEFAULT '', L9VARCHAR(40) DEFAULT '', L10 VARCHAR(40) DEFAULT '', L11 VARCHAR(40) DEFAULT '', L12 VARCHAR(40) DEFAULT '', L13 VARCHAR(40) DEFAULT '', L14 VARCHAR(40) DEFAULT '', L15 VARCHAR(40) DEFAULT '', L16 VARCHAR(40) DEFAULT '', NOM VARCHAR(40), AIGUA NUMERIC(10,2) DEFAULT 0, QSNUMERIC(10,2) DEFAULT 0, LLOGUER NUMERIC(10,2) DEFAULT 0, CANON NUMERIC(10,2) DEFAULT 0, MATERIALS NUMERIC(10,2) DEFAULT 0, IVA7 NUMERIC(10,2) DEFAULT 0, DESPESES NUMERIC(10,2) DEFAULT 0, FIANCA NUMERIC(10,2) DEFAULT 0, DRET NUMERIC(10,2) DEFAULT 0, DATACOBRAMENTDATE, CLAVEGUERAM NUMERIC(10,2) DEFAULT 0, QSCLAVEGUERAMNUMERIC(10,2) DEFAULT 0, DOCUMENT VARCHAR(10), DEVOLUCIONUMERIC(1)DEFAULT 0, ESTATVARCHAR(1), CARTANUMERIC(1)DEFAULT 0, ESPECIAL NUMERIC(1)DEFAULT 0, CONSTRAINT PK_PENDENTS PRIMARY KEY (DOCUMENT));
Re: [GENERAL] update error
So, what's the point of the alias in the documentation? -> http://www.postgresql.org/docs/current/static/sql-update.html 2009/4/24 Filip Rembiałkowski > > > 2009/4/24 josep porres > >> Hello, >> >> I am migrating a database to pg and modifying the app. >> In that process I found an error and i don't see the reason. >> >> It's happening when executing the next update sentence >> Do you see what's wrong? > > > table name / alias is not allowed in SET section. > > http://www.postgresql.org/docs/current/static/sql-update.html > > > Do not include the table's name in the specification of a target column — > for example, UPDATE tab SET tab.col = 1 is invalid. > > >> >> >> The server is pg8.3.7 for w32 >> >> >> update talls as t >> set t.estat=5, t.d_pag=now(), t.ts_update=now() >> where >> t.estat in (0,1) and t.data = '02/04/2009' >> and t.clau not in ( select p.clau from pendents p >> where (t.data - p.data) >= 90 ) >> >> ERROR: column "t" of relation "talls" does not exist >> LINE 3: set t.estat=5, t.d_pag=now(), t.ts_update=now() >> ^ >> >> ** Error ** >> >> ERROR: column "t" of relation "talls" does not exist >> estat SQL: 42703 >> Caràcter: 25 >> _ >> >> the table definitions are these: >> >> CREATE TABLE TALLS ( >> DATADATE NOT NULL, >> CLAUVARCHAR(12) NOT NULL, >> CLAU_ABOVARCHAR(12), >> LLIBRE INTEGER, >> FULLA NUMERIC(10) DEFAULT 0, >> NOM VARCHAR(40), >> ADRECA VARCHAR(40), >> NUMERCOMPT VARCHAR(15), >> LECACT NUMERIC(10) DEFAULT 0, >> LECTURA NUMERIC(10) DEFAULT 0, >> REFORMA NUMERIC(1) DEFAULT 0, >> MOTIU NUMERIC(1) DEFAULT 0, >> NOTES VARCHAR(100), >> D_TALL DATE, >> D_PAG DATE, >> D_RECON DATE, >> TS_UPDATE TIMESTAMP, >> ESTAT NUMERIC(2) DEFAULT 0, >> CPARAT NUMERIC(1) DEFAULT 0, >> NOUCOMPTVARCHAR(15), >> REINCID NUMERIC(1) DEFAULT 0, >> CPOSTAL NUMERIC(5), >> CONSTRAINT PK_TALLS PRIMARY KEY (DATA, CLAU)); >> >> CREATE TABLE PENDENTS ( >> CLAU VARCHAR(12), >> DATA DATE, >> TITULAR VARCHAR(40), >> BANC VARCHAR(4), >> SUCURSAL VARCHAR(4), >> COMPTEVARCHAR(10), >> IMPORTNUMERIC(10,2), >> L1VARCHAR(40) DEFAULT '', >> L2VARCHAR(40) DEFAULT '', >> L3VARCHAR(40) DEFAULT '', >> L4VARCHAR(40) DEFAULT '', >> L5VARCHAR(40) DEFAULT '', >> L6VARCHAR(40) DEFAULT '', >> L7VARCHAR(40) DEFAULT '', >> L8VARCHAR(40) DEFAULT '', >> L9VARCHAR(40) DEFAULT '', >> L10 VARCHAR(40) DEFAULT '', >> L11 VARCHAR(40) DEFAULT '', >> L12 VARCHAR(40) DEFAULT '', >> L13 VARCHAR(40) DEFAULT '', >> L14 VARCHAR(40) DEFAULT '', >> L15 VARCHAR(40) DEFAULT '', >> L16 VARCHAR(40) DEFAULT '', >> NOM VARCHAR(40), >> AIGUA NUMERIC(10,2) DEFAULT 0, >> QSNUMERIC(10,2) DEFAULT 0, >> LLOGUER NUMERIC(10,2) DEFAULT 0, >> CANON NUMERIC(10,2) DEFAULT 0, >> MATERIALS NUMERIC(10,2) DEFAULT 0, >> IVA7 NUMERIC(10,2) DEFAULT 0, >> DESPESES NUMERIC(10,2) DEFAULT 0, >> FIANCA NUMERIC(10,2) DEFAULT 0, >> DRET NUMERIC(10,2) DEFAULT 0, >> DATACOBRAMENTDATE, >> CLAVEGUERAM NUMERIC(10,2) DEFAULT 0, >> QSCLAVEGUERAMNUMERIC(10,2) DEFAULT 0, >> DOCUMENT VARCHAR(10), >> DEVOLUCIONUMERIC(1)DEFAULT 0, >> ESTATVARCHAR(1), >> CARTANUMERIC(1)DEFAULT 0, >> ESPECIAL NUMERIC(1)DEFAULT 0, >> CONSTRAINT PK_PENDENTS PRIMARY KEY (DOCUMENT)); >> >> > > > -- > Filip Rembiałkowski > JID,mailto:filip.rembialkow...@gmail.com > http://filip.rembialkowski.net/ >
