Обсуждение: [DOCS] nothing ever works

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html
Description:

Your documentation although it seems straight forward actually never
works.

The dynamic sql section gives example code, it does not execute.

It does not exec in a script block or as plain sql.

Please explain limitations or where the script can actually be executed.

As a beginner starting out in postgre you can never rely on the
documentation because the same thing happens, you copy the code and make
your modifications and you get syntax error.

The same problem exists with the Perform statement. It does not work as
advertised, you cannot execute it in a function or in a script block.

Clearly define the limitations of your language so that developers don't
waste their time.

Coming from ms sql quite frankly I would never recommend postgress. The
barrior to entry into actualy writing code is too great.

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

Here's an example. this code does not exec for me, I get syntax error. I have PG version 9.5

DO $$

BEGIN

        

EXEC SQL BEGIN DECLARE SECTION;

const char *stmt = "CREATE TABLE test1 (...);";

EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE IMMEDIATE :stmt;

  

END;

$$;

On Thu, 22 Jun 2017 at 09:21 Gerard Matthews <gerardmatt@gmail.com> wrote:

Hi Everyone,

Firstly I would like to thank everyone for your feedback. Secondly I would like to apologise, I had spent some time trying to write some dynamic SQL in postgres yesterday and got extremely frustrated at the the time I wasted. I realize this a community and I'm sorry for not being more constructive in my comment.

The reason for my frustration is this has happened before; where I look the examples and think I know how to use it only to run into syntax errors. I have tried the specific dynamic SQL examples inside script blocks and I get syntax errors. If it only works in functions it would be good to know that.

If it's not that then I wonder if perhaps the problem is PG Admin.

Often the way I learn with a new language is by trying to do something that I need. Hence I hit the docs at the point that I think will help me. If this happens with other developers perhaps it would be helpful for the examples to list where the specific language feature can be used.

I am not the only one as my colleagues have run into exactly the same thing.

Hopefully this input is a little more constructive and again thank you all for taking the time to respond.

On Thu, 22 Jun 2017 at 02:47 David G. Johnston <david.g.johnston@gmail.com> wrote:

>>> 2017-06-21 10:39 GMT+02:00 <gerardmatt@gmail.com>:
>>>
>>> Your documentation although it seems straight forward actually never
>>> works.

Most of it is not written as self-contained examples so this is not surprising.

>>> Please explain limitations or where the script can actually be executed.

That would be the responsibility of chapters 36 and 41.2 (and maybe
some others); chapters prior to the one you are complaining about.
Again, this isn't a cookbook format where every section and example is
self-contained.  This requires the reader to adopt their own
techniques for actually starting with functioning code and keeping it
functioning as new capabilities are introduced.  For better and worse
I don't foresee any volunteering significant time to change the style
of the documentation - particularly without a large volume of specific
complaints and/or suggestions to work from.

>>> As a beginner starting out in postgre you can never rely on the
>>> documentation because the same thing happens, you copy the code and make
>>> your modifications and you get syntax error.

Which means that some more fundamental aspect of the capability you
are trying to learn hasn't yet been figured out and so that code you
are writing is not operating in the context that the documentation
assumes it is.  You need to go back earlier in the
process/documentation and get the fundamentals out of the way.

>>> Clearly define the limitations of your language so that developers
>>> don&#39;t
>>> waste their time.

Either there is a distinct lack of others encountering the same
problems or they avail themselves of other solutions.  In any case the
authors of the documentation cannot foresee every confusion or problem
that may arise.  That's part of why the mailing lists exist.  Ideally
you'd come here, get un-stumped, look back at why you got stumped in
the first place, and suggest documentation improvements that would
help prevent the next person from being stumped in a similar matter.
That flow would, IMO, be in the true spirit of this open source
community.

>>> Coming from ms sql quite frankly I would never recommend postgress. The
>>> barrior to entry into actualy writing code is too great.

--file: create-functions.sql
--might not work as-is but the structural components you require are here.
CREATE FUNCTION remove_all_users() RETURNS SETOF bigint AS $$
DELETE FROM users RETURNING user_id;
$$ LANGUAGE SQL;

CREATE FUNCTION do_it() RETURNS void AS $$
BEGIN
RAISE NOTICE 'Performing User Removal';
PERFORM remove_all_users(); -- using perform since we don't care about
returned user_id's
END;
$$ LANGUAGE plpgsql;

$psql
>\i create-functions.sql
#now the two functions exist on the server
>SELECT do_it();
%NOTICE: Performing User Removal
do_it
-------

--this would be roughly equivalent but "DO" won't return a result
which SELECT do_it() does.
--still in psql...
DO $$
BEGIN
PERFORM remove_all_users();
END;
$$; --implied pl/pgsql language

I cannot speak to learning MS SQL compared to PostgreSQL; but in some
ways having existing, but difference, experience hurts since you are
apt to make assumptions about how things should work that are not
true.

Your welcome to your venting but all I see here is a specific case of
learning having gone into spiral.  The community here is great at
helping people get themselves out these kinds of spirals.  That the
documentation cannot do so all by itself is not a failing of the
documentation or its authors.

David J.

It's under this page on the docs https://www.postgresql.org/docs/9.1/static/ecpg-dynamic.html.

It does not make it clear where this code can be executed. It's in the documentation under chapter 33.5. Dynamic SQL. If you search in google for postgres dynamic sql it's the first link. Here is link to google search. https://www.google.co.za/search?q=postgresql+dynamic+sql&rlz=1C1CHZL_enZA685ZA685&oq=postgresql+dynamic+&aqs=chrome.0.0j69i57j0l4.8833j0j7&sourceid=chrome&ie=UTF-8

On Thu, 22 Jun 2017 at 12:37 Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-06-22 10:00 GMT+02:00 Gerard Matthews <gerardmatt@gmail.com>:

Here's an example. this code does not exec for me, I get syntax error. I have PG version 9.5

DO $$

BEGIN

        

EXEC SQL BEGIN DECLARE SECTION;

const char *stmt = "CREATE TABLE test1 (...);";

EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE IMMEDIATE :stmt;

  

END;

$$;

where you see this code?

It is part of embedded C, what is client side only code. You cannot to use embedded C on server side ever.

DO command is designed for execution server side procedures - you can use PLpgSQL, PLPythonu or PLPerl languages.

The functional example:

DO $$

DECLARE stmt text;
BEGIN

    stmt := 'CREATE TABLE ...()';

   EXECUTE stmt;

END;

$$ LANGUAGE plpgsql;

Related documentation: https://www.postgresql.org/docs/9.6/static/plpgsql.html

Maybe you are missing difference between client side and server side coding. Client side codes cannot to work on server side.

Regards

Pavel

On Thu, 22 Jun 2017 at 09:21 Gerard Matthews <gerardmatt@gmail.com> wrote:

Hi Everyone,

Firstly I would like to thank everyone for your feedback. Secondly I would like to apologise, I had spent some time trying to write some dynamic SQL in postgres yesterday and got extremely frustrated at the the time I wasted. I realize this a community and I'm sorry for not being more constructive in my comment.

The reason for my frustration is this has happened before; where I look the examples and think I know how to use it only to run into syntax errors. I have tried the specific dynamic SQL examples inside script blocks and I get syntax errors. If it only works in functions it would be good to know that.

If it's not that then I wonder if perhaps the problem is PG Admin.

Often the way I learn with a new language is by trying to do something that I need. Hence I hit the docs at the point that I think will help me. If this happens with other developers perhaps it would be helpful for the examples to list where the specific language feature can be used.

I am not the only one as my colleagues have run into exactly the same thing.

Hopefully this input is a little more constructive and again thank you all for taking the time to respond.

On Thu, 22 Jun 2017 at 02:47 David G. Johnston <david.g.johnston@gmail.com> wrote:

>>> 2017-06-21 10:39 GMT+02:00 <gerardmatt@gmail.com>:
>>>
>>> Your documentation although it seems straight forward actually never
>>> works.

Most of it is not written as self-contained examples so this is not surprising.

>>> Please explain limitations or where the script can actually be executed.

That would be the responsibility of chapters 36 and 41.2 (and maybe
some others); chapters prior to the one you are complaining about.
Again, this isn't a cookbook format where every section and example is
self-contained.  This requires the reader to adopt their own
techniques for actually starting with functioning code and keeping it
functioning as new capabilities are introduced.  For better and worse
I don't foresee any volunteering significant time to change the style
of the documentation - particularly without a large volume of specific
complaints and/or suggestions to work from.

>>> As a beginner starting out in postgre you can never rely on the
>>> documentation because the same thing happens, you copy the code and make
>>> your modifications and you get syntax error.

Which means that some more fundamental aspect of the capability you
are trying to learn hasn't yet been figured out and so that code you
are writing is not operating in the context that the documentation
assumes it is.  You need to go back earlier in the
process/documentation and get the fundamentals out of the way.

>>> Clearly define the limitations of your language so that developers
>>> don&#39;t
>>> waste their time.

Either there is a distinct lack of others encountering the same
problems or they avail themselves of other solutions.  In any case the
authors of the documentation cannot foresee every confusion or problem
that may arise.  That's part of why the mailing lists exist.  Ideally
you'd come here, get un-stumped, look back at why you got stumped in
the first place, and suggest documentation improvements that would
help prevent the next person from being stumped in a similar matter.
That flow would, IMO, be in the true spirit of this open source
community.

>>> Coming from ms sql quite frankly I would never recommend postgress. The
>>> barrior to entry into actualy writing code is too great.

--file: create-functions.sql
--might not work as-is but the structural components you require are here.
CREATE FUNCTION remove_all_users() RETURNS SETOF bigint AS $$
DELETE FROM users RETURNING user_id;
$$ LANGUAGE SQL;

CREATE FUNCTION do_it() RETURNS void AS $$
BEGIN
RAISE NOTICE 'Performing User Removal';
PERFORM remove_all_users(); -- using perform since we don't care about
returned user_id's
END;
$$ LANGUAGE plpgsql;

$psql
>\i create-functions.sql
#now the two functions exist on the server
>SELECT do_it();
%NOTICE: Performing User Removal
do_it
-------

--this would be roughly equivalent but "DO" won't return a result
which SELECT do_it() does.
--still in psql...
DO $$
BEGIN
PERFORM remove_all_users();
END;
$$; --implied pl/pgsql language

I cannot speak to learning MS SQL compared to PostgreSQL; but in some
ways having existing, but difference, experience hurts since you are
apt to make assumptions about how things should work that are not
true.

Your welcome to your venting but all I see here is a specific case of
learning having gone into spiral.  The community here is great at
helping people get themselves out these kinds of spirals.  That the
documentation cannot do so all by itself is not a failing of the
documentation or its authors.

David J.