On Fri, Mar 25, 2022 at 8:32 AM Philip Semanchuk <philip@xxxxxxxxxxxxxxxxxxxxx> wrote:
Here's the contents of foo.sql --
-- this is a comment
CREATE FUNCTION foo(bar text) RETURNS text AS $$
SELECT bar
$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE
;
When I feed that to 'psql -f foo.sql', the function is created as I expect. In the Postgres log, the leading comment *doesn't* appear. I see the same behavior if I just copy/paste the function into psql.
Our test system uses Python 3.8, SQLAlchemy 1.3.6, and psycopg 2.8.5, and when our test harness reads foo.sql and passes it to SQLAlchemy's execute(), I can see in the Postgres log that the leading comment is *not* stripped, and the function isn't created.
I think you need to provide these log entries you are referring to.
The comment form using the -- prefix ends at the first newline encountered. This is server behavior, not client-side [1]. But the comment doesn't actually belong with any individual command, it (the line) is simply ignored by the server when seen.
I suspect that the newline is being removed in order by SQLAlchemy to do all of its helpful stuff, like statement caching. I also suspect that you are probably mis-using the driver since the execute(string) method is marked deprecated [2], possibly for this very reason, but you also haven't shown that code so it is hard to say (I don't actually use the tool myself though).
David J.
