Re: check scripts after database code change

[Dennis]

> Who made what changes in what code?

I want to make special developments in the database code.

> Development of what and whose beta?

to check after making changes and customizations in the database code

>This is going to need a more detailed explanation before there can be an
answer more sophisticated then: maybe?

After I make changes to the code to customize the database  , I want to
find a check mechanism to detect the negative effects of my changes.


On Thu, Nov 18, 2021 at 7:07 PM Adrian Klaver 
wrote:

> On 11/18/21 06:45, Dennis wrote:
> > Hi,
> >
> > Are there any scripts that we can check after the changes we made in the
> > database code?
>
> Who made what changes in what code?
>
> > In summary, are there any automatic post-development scripts before they
> > come to the beta stage?
>
> Development of what and whose beta?
>
> This is going to need a more detailed explanation before there can be an
> answer more sophisticated then: maybe?
>
> >
> > Dennis
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: check scripts after database code change

[Ron]

There's a regression test suite. 
https://www.postgresql.org/docs/current/regress.html


On 11/19/21 2:01 AM, Dennis wrote:


> Who made what changes in what code?

I want to make special developments in the database code.

> Development of what and whose beta?

to check after making changes and customizations in the database code

>This is going to need a more detailed explanation before there can be an
answer more sophisticated then: maybe?

After I make changes to the code to customize the database  , I want to 
find a check mechanism to detect the negative effects of my changes.



On Thu, Nov 18, 2021 at 7:07 PM Adrian Klaver > wrote:


On 11/18/21 06:45, Dennis wrote:
> Hi,
>
> Are there any scripts that we can check after the changes we made in
the
> database code?

Who made what changes in what code?

> In summary, are there any automatic post-development scripts before
they
> come to the beta stage?

Development of what and whose beta?

This is going to need a more detailed explanation before there can be an
answer more sophisticated then: maybe?

>
> Dennis


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Angular momentum makes the world go 'round.

Re: check scripts after database code change

[Dennis]

Thanks Ron;
this is exactly what i was looking for.
I would like to know if there are different things like this
Dennis

On Fri, Nov 19, 2021 at 11:07 AM Ron  wrote:

> There's a regression test suite.
> https://www.postgresql.org/docs/current/regress.html
>
> On 11/19/21 2:01 AM, Dennis wrote:
>
>
> > Who made what changes in what code?
>
> I want to make special developments in the database code.
>
> > Development of what and whose beta?
>
> to check after making changes and customizations in the database code
>
> >This is going to need a more detailed explanation before there can be an
> answer more sophisticated then: maybe?
>
> After I make changes to the code to customize the database  , I want to
> find a check mechanism to detect the negative effects of my changes.
>
>
> On Thu, Nov 18, 2021 at 7:07 PM Adrian Klaver 
> wrote:
>
>> On 11/18/21 06:45, Dennis wrote:
>> > Hi,
>> >
>> > Are there any scripts that we can check after the changes we made in
>> the
>> > database code?
>>
>> Who made what changes in what code?
>>
>> > In summary, are there any automatic post-development scripts before
>> they
>> > come to the beta stage?
>>
>> Development of what and whose beta?
>>
>> This is going to need a more detailed explanation before there can be an
>> answer more sophisticated then: maybe?
>>
>> >
>> > Dennis
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
> --
> Angular momentum makes the world go 'round.
>

Re: Execute command in PL/pgSQL function not executing

[Abdul Mohammed]

Sorry for the late response and thank you Pavel for answering. This is my
first exposure to pgsql, so please bear with me. I am still getting the
Context message.  Here is the modified function:

CREATE OR REPLACE FUNCTION survey_ct ()
RETURNS SETOF text AS $$
DECLARE rec RECORD;
DECLARE str text;
BEGIN
str := '"participant_id" integer,';
   -- looping to get column heading string
   FOR rec IN SELECT DISTINCT text
FROM question
ORDER BY text
LOOP
str :=  str || '"' || rec.text || '" text' ||',';
END LOOP;
str:= substring(str, 0, length(str));

RETURN QUERY EXECUTE 'SELECT *
FROM crosstab(''select sp.participant_id, distinct qu.text, sr.text
from survey_progress sp
join question qu
on sp.survey_id = qu.survey_id
join survey_response sr
on qu.id = sr.question_id
where qu.question_type_id = 8
order by 1,2'')

 AS final_result ('|| str ||')';
RAISE NOTICE 'Got to the end of the function';
END;
$$ LANGUAGE plpgsql;

This gives: CONTEXT:  PL/pgSQL function survey_ct() line 15 at RETURN QUERY

Regards

On Thu, Nov 18, 2021 at 3:44 AM Pavel Stehule 
wrote:

>
>
> čt 18. 11. 2021 v 12:24 odesílatel Abdul Mohammed <
> imonikemoham...@gmail.com> napsal:
>
>> Hello everyone,
>> Please I am having a problem with a function I am writing. The first part
>> uses a loop that pulls the values from a column and concatenates them into
>> a string. This first part works fine. The second part tries to use the
>> string to build a pivot table using the crosstab function. The function is
>> as follows:
>>
>> CREATE OR REPLACE FUNCTION field_values_ct ()
>> RETURNS VOID AS $$
>> DECLARE rec RECORD;
>> DECLARE str text;
>> BEGIN
>> str := '"participant_id" integer,';
>>-- looping to get column heading string
>>FOR rec IN SELECT DISTINCT text
>> FROM question
>> ORDER BY text
>> LOOP
>> str :=  str || '"' || rec.text || '" text' ||',';
>> END LOOP;
>> str:= substring(str, 0, length(str));
>>
>> EXECUTE 'SELECT *
>> FROM crosstab(''select sp.participant_id, distinct qu.text, sr.text
>> from survey_progress sp
>> join question qu
>> on sp.survey_id = qu.survey_id
>> join survey_response sr
>> on qu.id = sr.question_id
>> where qu.question_type_id = 8
>> order by 1,2'')
>>
>>  AS final_result ('|| str ||')';
>> RAISE NOTICE 'Got to the end of the function';
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> The Execute Select statement doesn't seem to execute. There aren't any
>> error or hint messages either. It only prints a context message as follows:
>>
>> CONTEXT:  PL/pgSQL function field_values_ct() line 15 at EXECUTE
>>
>> Please I would be very grateful for any hints as to what I could be doing
>> wrong.
>>
>
> This is not MS SQL - result of last query is not result of function.
>
> When you want to see result, you should to use RETURN statement - in this
> case RETURN QUERY EXECUTE, and your function should to return SETOF text
> instead VOID.
>
> Regards
>
> Pavel Stehule
>
>>
>> Regards
>>
>>
>>

Re: Execute command in PL/pgSQL function not executing

[Tom Lane]

Abdul Mohammed  writes:
> Sorry for the late response and thank you Pavel for answering. This is my
> first exposure to pgsql, so please bear with me. I am still getting the
> Context message.  Here is the modified function:

I tried this, and after creating a dummy "question" table I got

ERROR:  structure of query does not match function result type
DETAIL:  Returned type integer does not match expected type text in column 1.
CONTEXT:  SQL statement "SELECT *
FROM crosstab('select sp.participant_id, distinct qu.text, sr.text
from survey_progress sp
join question qu
on sp.survey_id = qu.survey_id
join survey_response sr
on qu.id = sr.question_id
where qu.question_type_id = 8
order by 1,2')

 AS final_result ("participant_id" integer,"what" text,"when" 
text,"who" text)"
PL/pgSQL function survey_ct() line 15 at RETURN QUERY

It's slightly surprising to me that this problem gets detected
before noticing that the embedded query is invalid (it's
syntactically incorrect because of the bogus placement of
"distinct", even before getting to the fact that I didn't
make a survey_progress table).  Nonetheless, it's a real
problem with the way you're trying to use crosstab().
Given that the output of crosstab() will be an integer
column followed by some text columns, you can't just
"return query" in a calling function whose output is
specified to be a single text column.

Anyway, I'd make the following recommendations:

1. You need to get used to Postgres error message layout.
You're apparently focusing only on the last line of context,
which is about the least important part of the report.

2. I'd suggest debugging the base query before trying to
wrap it in a crosstab() call, and then testing the crosstab()
manually before you wrap it in a plpgsql function.  That
would give you a better feeling for the work that
the plpgsql function has to do.

3. I kind of doubt that outputting a single text column
is really the end result you want here.  How is it
meaningful to be crosstab'ing multiple questions if
that's to be the end result?

regards, tom lane

Re: Execute command in PL/pgSQL function not executing

[David G. Johnston]

On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed 
wrote:

> The second part tries to use the string to build a pivot table using the
> crosstab function.
>

Aside from the simple learning curve on how to write functions in
PostgreSQL you also are dealing with the fact that you are dealing with a
query that has a variable number of columns and that is just not something
that PostgreSQL allows.  It is in fact the reason the crosstab function
itself has to use the convoluted record return syntax where the caller has
to declare how many columns the function is going to return.  You are
trying to get around this by having the function scan the table at runtime
to figure out which columns it needs to declare.  But that won't work
because the function itself still has to know how many columns it is going
to output when it is called.

Personally I've found two solutions to this.  Do the dynamic part in
application code and just send the dynamic SQL to the server for
execution.  Or, turn your output into a container type (I've used CSV in
the past but JSON probably works better) and just return the complex value
from the function; then the application just has to deal with a simple
decomposing of the complex value into the final table it represents.

David J.

Re: Execute command in PL/pgSQL function not executing

[Abdul Mohammed]

Thank you very much for your response Tom. I will follow your
recommendations. As for the 3rd one, I am actually trying to output the
pivot table I am trying to create using crosstab. I read somewhere that you
could have a table as a return type. I, therefore tried using the pivot
table alias as the return type but got an error along lines of the alias
not being recognized.

Much regards

On Fri, Nov 19, 2021 at 12:59 PM Tom Lane  wrote:

> Abdul Mohammed  writes:
> > Sorry for the late response and thank you Pavel for answering. This is my
> > first exposure to pgsql, so please bear with me. I am still getting the
> > Context message.  Here is the modified function:
>
> I tried this, and after creating a dummy "question" table I got
>
> ERROR:  structure of query does not match function result type
> DETAIL:  Returned type integer does not match expected type text in column
> 1.
> CONTEXT:  SQL statement "SELECT *
> FROM crosstab('select sp.participant_id, distinct qu.text, sr.text
> from survey_progress sp
> join question qu
> on sp.survey_id = qu.survey_id
> join survey_response sr
> on qu.id = sr.question_id
> where qu.question_type_id = 8
> order by 1,2')
>
>  AS final_result ("participant_id" integer,"what" text,"when"
> text,"who" text)"
> PL/pgSQL function survey_ct() line 15 at RETURN QUERY
>
> It's slightly surprising to me that this problem gets detected
> before noticing that the embedded query is invalid (it's
> syntactically incorrect because of the bogus placement of
> "distinct", even before getting to the fact that I didn't
> make a survey_progress table).  Nonetheless, it's a real
> problem with the way you're trying to use crosstab().
> Given that the output of crosstab() will be an integer
> column followed by some text columns, you can't just
> "return query" in a calling function whose output is
> specified to be a single text column.
>
> Anyway, I'd make the following recommendations:
>
> 1. You need to get used to Postgres error message layout.
> You're apparently focusing only on the last line of context,
> which is about the least important part of the report.
>
> 2. I'd suggest debugging the base query before trying to
> wrap it in a crosstab() call, and then testing the crosstab()
> manually before you wrap it in a plpgsql function.  That
> would give you a better feeling for the work that
> the plpgsql function has to do.
>
> 3. I kind of doubt that outputting a single text column
> is really the end result you want here.  How is it
> meaningful to be crosstab'ing multiple questions if
> that's to be the end result?
>
> regards, tom lane
>

Re: Execute command in PL/pgSQL function not executing

[Abdul Mohammed]

Many thanks for the explanations David.
My failed attempts and your explanations have forced me to realise that I
need to spend some time learning to walk before attempting to run.

Much regards

On Fri, Nov 19, 2021 at 1:31 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed 
> wrote:
>
>> The second part tries to use the string to build a pivot table using the
>> crosstab function.
>>
>
> Aside from the simple learning curve on how to write functions in
> PostgreSQL you also are dealing with the fact that you are dealing with a
> query that has a variable number of columns and that is just not something
> that PostgreSQL allows.  It is in fact the reason the crosstab function
> itself has to use the convoluted record return syntax where the caller has
> to declare how many columns the function is going to return.  You are
> trying to get around this by having the function scan the table at runtime
> to figure out which columns it needs to declare.  But that won't work
> because the function itself still has to know how many columns it is going
> to output when it is called.
>
> Personally I've found two solutions to this.  Do the dynamic part in
> application code and just send the dynamic SQL to the server for
> execution.  Or, turn your output into a container type (I've used CSV in
> the past but JSON probably works better) and just return the complex value
> from the function; then the application just has to deal with a simple
> decomposing of the complex value into the final table it represents.
>
> David J.
>

Re: Execute command in PL/pgSQL function not executing

[David G. Johnston]

On Fri, Nov 19, 2021 at 3:37 PM Abdul Mohammed 
wrote:

>  I read somewhere that you could have a table as a return type. I,
> therefore tried using the pivot table alias as the return type but got an
> error along lines of the alias not being recognized.
>

A table in that context is something you create using the "Create table"
command.  And, per my other comments, you have to have done this before
even creating the function in question since the function has to point to
the table during the "create function" command's execution.

David J.

Re: Execute command in PL/pgSQL function not executing

[Marc Millas]

Hi,
Just one question
Isn't it possible to write a function that return an oid?
So.. One can write a function which create a temp table, feed it according
to parameters and then return the oid of the created temp table.

No??

Le ven. 19 nov. 2021 à 23:48, David G. Johnston 
a écrit :

> On Fri, Nov 19, 2021 at 3:37 PM Abdul Mohammed 
> wrote:
>
>>  I read somewhere that you could have a table as a return type. I,
>> therefore tried using the pivot table alias as the return type but got an
>> error along lines of the alias not being recognized.
>>
>
> A table in that context is something you create using the "Create table"
> command.  And, per my other comments, you have to have done this before
> even creating the function in question since the function has to point to
> the table during the "create function" command's execution.
>
> David J.
>
>

Re: Execute command in PL/pgSQL function not executing

[Michael Lewis]

You could also just return the name of the temp table, or return nothing
and directly use that name manually outside the function to do select *
from pg_temp.my_data