Re: nothing ever works

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

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