Re: nothing ever works

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