2017-06-22 10:00 GMT+02:00 Gerard Matthews <gerardm...@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
> [image: image.png]
>
>
> On Thu, 22 Jun 2017 at 09:21 Gerard Matthews <gerardm...@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.johns...@gmail.com> wrote:
>>
>>> >>> 2017-06-21 10:39 GMT+02:00 <gerardm...@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'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.
>>>
>>
