Debugging PostgreSQL

Hi Dave,

This was the original post

CREATE OR REPLACE FUNCTION usp_PG_DUMPTEMPTABLE(varchar(100), text[])
RETURNS void AS
$BODY$
DECLARE
_temptable ALIAS FOR $1;
_temparray alias for $2;
_i integer;
_max integer;
_tempstring varchar(2000);

BEGIN
_tempstring := 'RAISE INFO''';
for _i in 1 ..array_upper(_temparray, 1)
loop
_tempstring := _tempstring || _temparray[_i] || ' ' ;
end loop;
_tempstring := _tempstring || ''';';

raise info'%', _tempstring;
execute _tempstring;
raise info '---';

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';

I need to build the Raise Info dynamically because it's params are dependent on the array passed into the function. I can't think of any other way to do this. I can change the logic to use cursors but even then, I would still need to build the raise info command dynamically. Since Execute cannot call Raise...well then the idea is kaputz.

I think things got confused when I posted :
'
BTW just a simple :
execute 'RAISE INFO ''test ''';

has an error...am still searching docs everywhere..
'
You see when I ran and debugged the function...execute _tempstring gave an error :

ERROR: syntax error at or near "RAISE"
LINE 1: RAISE DEBUG 'SKUID CatID ';

I couldn't figure out why....so a simple test would be just to execute a literal string (instead of a variable holding a dynamic one) with a raise command...turns out execute cannot run the raise command...

Thanks.

Regards,
calhoun

Hi,

The only problem I see with the function above is that you've missed some spaces in a couple of places - most importantly, this:

should be

Note the extra space after 'raise info'. Also, _tempstring := 'RAISE INFO'''; should probably have a space after INFO as well.

Having fixed that, and commented out the 'execute _tempstring' (which you don't need because RAISE can always handle dynamic strings), I get the following:

I would also make an unrelated suggestion - because Postgres folds unquoted identifiers to lower case, it's much easier to stick to lower case all the time rather than use mixed or upper. Trust me, it'll be easier in the long run, even if it does seem a little odd at first

Hi dave, thanks for the help...please consider the function and comments below. Thanks again for all the help....

create or replace function usf_temp()
returns void as
$BODY$
declare
_i integer;
_max integer;
_tempstring text;
_tempstring1 text;
_tempstring2 text;

_tstval1 text;
_tstval2 text;

begin

_tstval1 := 'THIS IS VALUE 1';
_tstval2 := 'ANOTHER VALUE FOR YOU';

-- this is just a test function for usp_PG_DUMPTABLE
-- note that in usp_PG_DUMPTABLE, we'd need to build the format field of raise dynamically since it will be dependent
-- on the array input parameter for the number of % characters within... if the array had 2 elements then '% %',
-- if the array had 3 elements then '% % %'
--- for each of the % character, in the format field, there must be a variable...test this out first if this works...

_tempstring := '''% | %''';

---test the contents of the variable...remove this later

raise info '%', _tempstring;

-- test a 'proper' raise info first...

raise info '% | %', _tstval1, _tstval2;

-- this will obviously work

-- now test that the format field of the raise info uses a dynamic string variable...

raise info _tempstring, _tstval1, _tstval2;

--errors here....raise cannot handle dynamic strings on the first parameter?
-- which means the core idea for usp_PG_DUMPTABLE cannot work since the format field there must be built dynamically to handle the
-- changing number of elements in the array parameter...

raise info '---';

return;
end;
$BODY$
language 'plpgsql';

select usf_temp();

calhoun wrote:Hi dave, thanks for the help...please consider the function and comments below. Thanks again for all the help....

You're welcome.

calhoun wrote:
_tstval1 := 'THIS IS VALUE 1';
_tstval2 := 'ANOTHER VALUE FOR YOU';

-- this is just a test function for usp_PG_DUMPTABLE
-- note that in usp_PG_DUMPTABLE, we'd need to build the format field of raise dynamically since it will be dependent
-- on the array input parameter for the number of % characters within... if the array had 2 elements then '% %',
-- if the array had 3 elements then '% % %'
--- for each of the % character, in the format field, there must be a variable...test this out first if this works...

_tempstring := '''% | %''';

---test the contents of the variable...remove this later

raise info '%', _tempstring;

-- test a 'proper' raise info first...

raise info '% | %', _tstval1, _tstval2;

-- this will obviously work

-- now test that the format field of the raise info uses a dynamic string variable...

raise info _tempstring, _tstval1, _tstval2;

--errors here....raise cannot handle dynamic strings on the first parameter?
-- which means the core idea for usp_PG_DUMPTABLE cannot work since the format field there must be built dynamically to handle the
-- changing number of elements in the array parameter...

Maybe it's because it's Friday evening and I'm feeling a little burned out, but I don't understand why you don't just build the output string for each row using the array upperbound to control a loop as you did in your previous example. In pseudocode, something like:

If you're columns aren't fixed width then you might need a simple function to space-pad them to the same length, but that should be fairly trivial.

Hope this helps

Dave.