Re: export import bytea from psql

2017-05-09 23:00 GMT+02:00 Daniel Verite <daniel(at)manitou-mail(dot)org>:

> Pavel Stehule wrote:
>
> > 1. using psql variables - we just need to write commands \setfrom
> > \setfrombf - this should be very simple implementation. The value can be
> > used more times. On second hand - the loaded variable can hold lot of
> > memory (and it can be invisible for user).
>
> This could be simplified by using the variable only for the filename,
> and then injecting the contents of the file into the PQexec'd query
> as the interpolation of the variable.
> We already have:
> :var for verbatim injection
> :'var' for injection quoted-as-text
> :"var" for injection quoted-as-identifier
>
> What if we add new forms of dereferencing, for instance
> (not necessarily with this exact syntax):
> :<var> for injecting the quoted-as-text contents of the file pointed
> to by var.
> :{var} same thing but with file contents quoted as binary
> (PQescapeByteaConn)
>
> then we could write:
>
> \set img '/path/to/image.png'
> insert into image(binary) values(:{img});
>

It is similar to my first or second proposal - rejected by Tom :(

>
> We could also go further in that direction. More new interpolation
> syntax could express that a variable is to be passed as a
> parameter rather than injected (assuming a parametrized query),
> and whether the value is directly the contents or it's a filename
> pointing to the contents, and whether its format is binary or text,
> and even support an optional oid or typename coupled to
> the variable.
> That would be a lot of new syntax for interpolation but no new
> backslash command and no change to \set itself.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>