Re: nothing ever works

2017-06-22 13:29 GMT+02:00 Gerard Matthews <gerardmatt(at)gmail(dot)com>:

> 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
>

Unfortunately we have not control of Google. I understand so every body can
be confused from this information. ecpg is old very specific feature of old
databases. Few people uses it today.

For you interesting page is
https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

but you need to use keyword "plpgsql" what is preferred language for stored
procedures in Postgres. I don't know why Google is thinking so ecpg is more
than plpgsql :(.

Regards

Pavel

>
> On Thu, 22 Jun 2017 at 12:37 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> 2017-06-22 10:00 GMT+02:00 Gerard Matthews <gerardmatt(at)gmail(dot)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
>>
>>
>>> [image: image.png]
>>>
>>>
>>> On Thu, 22 Jun 2017 at 09:21 Gerard Matthews <gerardmatt(at)gmail(dot)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(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>>
>>>>> >>> 2017-06-21 10:39 GMT+02:00 <gerardmatt(at)gmail(dot)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.
>>>>>
>>>>