Thread

1. BUG #18989: Output of \sf does not match original source for E quoted strings (unlike \df+ used to)

   PG Bug reporting form <noreply@postgresql.org> — 2025-07-17T10:15:47Z

   The following bug has been logged on the website:
   
   Bug reference:      18989
   Logged by:          Tim Colles
   Email address:      tim.colles@ed.ac.uk
   PostgreSQL version: 16.9
   Operating system:   Ubuntu Jammy
   Description:        
   
   psql testdb
   psql (16.9 (Ubuntu 16.9-1.pgdg22.04+1))
   Type "help" for help.
   
   testdb=# create function test() returns text language sql begin atomic
   select E'\x12' || 'Hello World' || E'\x12'; end;
   CREATE FUNCTION
   
   testdb=# \sf test
   CREATE OR REPLACE FUNCTION public.test()
    RETURNS text
    LANGUAGE sql
   BEGIN ATOMIC
    SELECT ((''::text || 'Hello World'::text) || ''::text);
   END
   
   Hence if the output of \sf is used as the source to make some amendment to
   the function the result could easily end up unintentionally different to the
   original.
   
   Not sure if this is a bug or expected behaviour, but noting that in v14 the
   output of \df+ would have been:
   
    Schema | Name | Result data type | Argument data types | Type | Volatility
   | Parallel |  Owner   | Security | Access privileges | Language |
   Source code                            | Description
   --------+------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+------------------------------------------------------------------+-------------
    public | test | text             |                     | func | volatile
   | unsafe   | postgres | invoker  |                   | sql      | BEGIN
   ATOMIC                                                    +|
           |      |                  |                     |      |
   |          |          |          |                   |          |  SELECT
   (('\x12'::text || 'Hello World'::text) || '\x12'::text);+|
           |      |                  |                     |      |
   |          |          |          |                   |          | END
   |
   
   i.e. the original source is retained better (although still minus the E
   prefix on the literal).
   
   If this is expected behaviour perhaps the documentation
   (https://www.postgresql.org/docs/current/app-psql.html) which currently says
   under \df+ that "Source code for a specific function can be seen using \sf"
   could be altered to say something like "an approximation of the source
   code", with a warning about using it to directly amend the function?
   
   

2. Re: BUG #18989: Output of \sf does not match original source for E quoted strings (unlike \df+ used to)

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-17T13:58:44Z

   PG Bug reporting form <noreply@postgresql.org> writes:
   > testdb=# create function test() returns text language sql begin atomic
   > select E'\x12' || 'Hello World' || E'\x12'; end;
   > CREATE FUNCTION
   
   > testdb=# \sf test
   > CREATE OR REPLACE FUNCTION public.test()
   >  RETURNS text
   >  LANGUAGE sql
   > BEGIN ATOMIC
   >  SELECT ((''::text || 'Hello World'::text) || ''::text);
   > END
   
   > Hence if the output of \sf is used as the source to make some amendment to
   > the function the result could easily end up unintentionally different to the
   > original.
   
   [ shrug... ]  I do not see a bug here.  The control characters are
   there; if your editor mangles them, that's your editor's fault.
   There are a ton of related scenarios where you could fear that
   non-ASCII characters might get mangled during query editing.
   If we tried to prevent them all, we'd make the editing experience
   less pleasant not more so.
   
   Personally I'd probably write chr(12) instead of what you did here.
   
   			regards, tom lane