Re: How to write such a query?

[Igor Korot]

Hi, Armul,

On Thu, Jan 6, 2022 at 12:46 AM Amul Sul  wrote:
>
> See prepare statement : 
> https://www.postgresql.org/docs/current/sql-prepare.html

The documentation is talking about a way to do it like:

SELECT a, b, c FROM foo WHERE id = $1,

which is equivalent to the

SELECT a, b, c FROM foo WHERE id = ?;

i.e. using unnamed parameter.

Thank you.

>
> On Thu, Jan 6, 2022 at 12:10 PM Igor Korot  wrote:
> >
> > Hi, ALL,
> > In SQLite you can write:
> >
> > SELECT a, b, c FROM foo WHERE id = :id;
> >
> > where ":id" is the named parameter.
> >
> > The query above is similar to
> >
> > SELECT a,b,c FROM foo WHERE id = ?;
> >
> > except that the parameter has a name.
> >
> > Is there a way to write such a SELECT statement with the
> > named parameter in PostgreSQL?
> >
> > Thank you.
> >
> >

Re: How to write such a query?

[Ron]

On 1/6/22 1:06 AM, Igor Korot wrote:

Hi, Ron,

On Thu, Jan 6, 2022 at 1:01 AM Ron  wrote:

On 1/6/22 12:39 AM, Igor Korot wrote:

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?

Absolutely.  Of course, the exact method depends on the client.  Are you
referring to psql?

If so, then here's an example:
$ psql12 -v S=456789012 test
psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
Type "help" for help.

test=# select * from employee where ssn = :'S';
  ssn|  name   |  ssn_int
---+-+---
   456789012 | Fred Flintstone | 456789012
(1 row)

In this case, column ssn is of type varchar(9).

Is the syntax available since 9.0+? Or later?
I'm writing C++.


Like I said before... "the exact method depends on the client".  The C++ 
client */library/* is not the same as the psql *application*.


IOW, read the C+ client library documentation.

--
Angular momentum makes the world go 'round.

Re: How to write such a query?

[Ron]

On 1/6/22 1:07 AM, Igor Korot wrote:

Hi, David,

On Thu, Jan 6, 2022 at 1:00 AM David G. Johnston
 wrote:

On Wednesday, January 5, 2022, Igor Korot  wrote:


Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?


The server, and its prepared SQL capability, doesn’t understand named 
parameters.  Only numbered ones.

That said, there are a number of different ways to write and execute SQL 
available to you and each of those provides different extended capabilities.  
For instance, psql and its variables feature.

It looks like your answer contradicts with Ron...


TIMTOWTDI

--
Angular momentum makes the world go 'round.

Re: How to write such a query?

[Igor Korot]

Hi, David,

On Thu, Jan 6, 2022 at 1:00 AM David G. Johnston
 wrote:
>
> On Wednesday, January 5, 2022, Igor Korot  wrote:
>>
>>
>> Is there a way to write such a SELECT statement with the
>> named parameter in PostgreSQL?
>
>
> The server, and its prepared SQL capability, doesn’t understand named 
> parameters.  Only numbered ones.
>
> That said, there are a number of different ways to write and execute SQL 
> available to you and each of those provides different extended capabilities.  
> For instance, psql and its variables feature.

It looks like your answer contradicts with Ron...

Thank you.

>
> David J.
>

Re: How to write such a query?

[Igor Korot]

Hi, Ron,

On Thu, Jan 6, 2022 at 1:01 AM Ron  wrote:
>
> On 1/6/22 12:39 AM, Igor Korot wrote:
> > Hi, ALL,
> > In SQLite you can write:
> >
> > SELECT a, b, c FROM foo WHERE id = :id;
> >
> > where ":id" is the named parameter.
> >
> > The query above is similar to
> >
> > SELECT a,b,c FROM foo WHERE id = ?;
> >
> > except that the parameter has a name.
> >
> > Is there a way to write such a SELECT statement with the
> > named parameter in PostgreSQL?
> Absolutely.  Of course, the exact method depends on the client.  Are you
> referring to psql?
>
> If so, then here's an example:
> $ psql12 -v S=456789012 test
> psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
> Type "help" for help.
>
> test=# select * from employee where ssn = :'S';
>  ssn|  name   |  ssn_int
> ---+-+---
>   456789012 | Fred Flintstone | 456789012
> (1 row)
>
> In this case, column ssn is of type varchar(9).

Is the syntax available since 9.0+? Or later?
I'm writing C++.

Thank you.

>
> --
> Angular momentum makes the world go 'round.
>
>

Re: How to write such a query?

[Ron]

On 1/6/22 12:39 AM, Igor Korot wrote:

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?
Absolutely.  Of course, the exact method depends on the client.  Are you 
referring to psql?


If so, then here's an example:
$ psql12 -v S=456789012 test
psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
Type "help" for help.

test=# select * from employee where ssn = :'S';
    ssn    |  name   |  ssn_int
---+-+---
 456789012 | Fred Flintstone | 456789012
(1 row)

In this case, column ssn is of type varchar(9).

--
Angular momentum makes the world go 'round.

Re: How to write such a query?

[David G. Johnston]

On Wednesday, January 5, 2022, Igor Korot  wrote:
>
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
>

The server, and its prepared SQL capability, doesn’t understand named
parameters.  Only numbered ones.

That said, there are a number of different ways to write and execute SQL
available to you and each of those provides different extended
capabilities.  For instance, psql and its variables feature.

David J.

Re: How to write such a query?

[Amul Sul]

See prepare statement : https://www.postgresql.org/docs/current/sql-prepare.html

On Thu, Jan 6, 2022 at 12:10 PM Igor Korot  wrote:
>
> Hi, ALL,
> In SQLite you can write:
>
> SELECT a, b, c FROM foo WHERE id = :id;
>
> where ":id" is the named parameter.
>
> The query above is similar to
>
> SELECT a,b,c FROM foo WHERE id = ?;
>
> except that the parameter has a name.
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
>
> Thank you.
>
>

How to write such a query?

[Igor Korot]

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?

Thank you.

Re: How best to create and use associative array type in Postgres?

[Adrian Klaver]

On 1/5/22 12:26, Shaozhong SHI wrote:

Have a look at this one.

GitHub - theory/kv-pair: A key/value pair data type for PostgreSQL 



There is no documentation on how to use it.


Why use it when you have:

https://www.postgresql.org/docs/current/datatype-json.html

Or if you want something simpler:

https://www.postgresql.org/docs/current/hstore.html



Regards,

David

On Wed, 5 Jan 2022 at 16:24, Tom Lane > wrote:


Shaozhong SHI mailto:shishaozh...@gmail.com>> writes:
 > What do you think this attempt by using create type and create a
function?
 > Managing Key/Value Pairs in PostgreSQL (justatheory.com
)
 > >

The date alone should suggest to you that there might be
better ways to do it by now.

                         regards, tom lane




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How best to create and use associative array type in Postgres?

[Shaozhong SHI]

Have a look at this one.

GitHub - theory/kv-pair: A key/value pair data type for PostgreSQL


There is no documentation on how to use it.

Regards,

David

On Wed, 5 Jan 2022 at 16:24, Tom Lane  wrote:

> Shaozhong SHI  writes:
> > What do you think this attempt by using create type and create a
> function?
> > Managing Key/Value Pairs in PostgreSQL (justatheory.com)
> > 
>
> The date alone should suggest to you that there might be
> better ways to do it by now.
>
> regards, tom lane
>

Re: Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

[Tom Lane]

[ please keep the list cc'd ]

Dominique Devienne  writes:
> On Wed, Jan 5, 2022 at 7:26 PM Tom Lane  wrote:
>> That will only revoke privileges that were granted by the role
>> doing the REVOKE, so I surmise that you did this as some other role.

> Hmmm, that's interesting... But it brings up something I didn't think of.
> The point of making a NOLOGIN ROLE for the owner of all DB objects,
> is to be able to GRANT that ROLE to several LOGIN USERs, which are
> all basicall Administrators of that system. So any one of them should be
> able to DROP the whole shebang, but what you are saying seems to be
> that only the admin that *originally* created the "shebang" can get rid of
> "owner" ROLE, or a SUPERUSER of course. Is that correct?

The admins would need to be careful to SET ROLE to the shared role
when manipulating privileges granted by that role --- either to
grant them, or to revoke them.

Alternatively, they could be careful to specify "GRANTED BY shared_role"
in all these commands.  But I think our coverage for that option isn't
complete, especially in older branches.

(IIRC there is some hack that causes superuser-issued revokes
to nuke privileges granted by the object's owner, even without an
explicit GRANTED BY.  But that doesn't apply to ordinary users.)

regards, tom lane

Re: Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

[Tom Lane]

Dominique Devienne  writes:
> Final step is to DROP the "owner" ROLE itself, but before doing that,
> I run REVOKE ALL ON DATABASE "ddevienne" FROM "owner".

That will only revoke privileges that were granted by the role
doing the REVOKE, so I surmise that you did this as some other role.
But actually capturing the DB's privileges at the point where the
DROP ROLE is failing would help you debug.

regards, tom lane

Re: Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

[Rob Sargent]

> 
> Any help would be appreciated in diagnosing this vexing issue. Thanks, --DD

Have you captured all the DDL generated by your C++ code?

Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

[Dominique Devienne]

At least I think I've revoked the privileges...

Hi. I'm writing a unit test that creates a set of schemas and roles,
then drops all those roles and schemas.

First I create a NOINHERIT NOLOGIN CREATEROLE "owner" ROLE,
which I GRANT on the current user (and other LOGIN users later), then SET
ROLE "owner",
and create all the SCHEMAs (and there associated ROLEs),
to have them (the SCHEMAs) owned by "owner".

To be able to do that, I of course GRANT CREATE ON DATABASE "ddevienne" TO
"owner".

All ROLEs (including the "owner") have a GRANT USAGE ON SCHEMA, for all
created SCHEMAs. (I use the ACLs on the SCHEMAs to find their associated
ROLEs, in addition to naming conventions).

And they also get a ALTER DEFAULT PRIVILEGES IN SCHEMA ... GRANT $priv TO
$role,
where $priv is for example "SELECT ON TABLES", "USAGE, SELECT ON SEQUENCES",
"EXECUTE ON ROUTINES", "USAGE ON TYPES".

That's for the ROLEs to implicitly get GRANTs on new objects created in
those schemas.
(I actually do that right after the CREATE SCHEMA, and before doing all the
CREATE TABLE DDLs, but it's mostly meant for later SCHEMA changes, to have
the ROLE "auto-updated")

And I do all those creations in a first transaction. So far so good.

Then right away, the unit test tries to tear down all objects, in another
transaction.

First, I start with a SET ROLE "owner" again (not sure it's necessary).
Then a DROP OWNED BY "owner" CASCADE to get rid of all SCHEMAs.
(since they are all owned by "owner", on purpose).
Then I find and get rid of all ROLEs (associated to SCHEMAs, via USAGE
ACLs, see above) except "owner", by DROP'ing them.
Again, so far, so good.

Final step is to DROP the "owner" ROLE itself, but before doing that,
I run REVOKE ALL ON DATABASE "ddevienne" FROM "owner".
(that ROLE has no other privileges in other databases. It's created on the
fly).

Then I RESET ROLE, since one cannot DROP the current_role.
So the current ROLE is back to the LOGIN USER.

But despite both steps above, the DROP ROLE "owner" fails...
Here's the error I get:

ERROR:  role "Epos-PNS (9e8a5159b0a64c02a40358431af1aa00)" cannot be
dropped because some objects depend on it
DETAIL:  privileges for database ddevienne

Given that just REVOKE'd ALL on the DB just before, I don't understand that
message.
I'm in C++, and any libpq error throws an exception, which implicitly
ROLLBACK's the transaction, thus leaving / leaking all my SCHEMAs and ROLEs
around.

And what's even Weirder, is that when I go in PSQL, and manually clean up
the mess left behind by the unit test that failed, I have no problem
DROP'ing those "owner" ROLEs. E.g.

ddevienne=> drop owned by "Epos-PNS (cbee4676ba7f428da29d36e1b8a4d523)";
DROP OWNED
Time: 27.607 ms
ddevienne=> drop role "Epos-PNS (cbee4676ba7f428da29d36e1b8a4d523)";
DROP ROLE
Time: 0.852 ms

These are *exactly* the same steps the unit tests goes through, yet in PSQL
the 2nd works fine!
Except perhaps I didn't run those inside a transaction. In the code, I
tried doing the DROP ROLE "owner" after COMMIT'ing the transaction, and
that didn't help (but at least left just a single ROLE to cleanup manually,
since the COMMIT succeeds).

So at this point, I'm kinda baffled, and need some advice.
Could there be some kind of race that the REVOKE ALL on the DB is not yet
"effective"?
I tried adding a SLEEP(1s) in the code, and that didn't help.

What kind of QUERY should I run in the code, to assertain what kind of
privileges that ROLE still has? Or what other objects could be depending on
the "owner" ROLE?

Any help would be appreciated in diagnosing this vexing issue. Thanks, --DD

Re: Can we use sql language to create a function in Postgres?

[Merlin Moncure]

On Wed, Jan 5, 2022 at 5:27 AM Shaozhong SHI  wrote:
>
> Any online documentation or examples for using Oracle SQL in Postgres?

FYI there are commercial offerings (in particular, EDB, which I am not
affiliated with) that minimize compatibility concerns with oracle.  If
doing this by hand, utilizing the lists (which SPECIFIC questions) and
other internet resources is the way to go. There may be some guides,
but the SQL language and other nuance is moving fast, so try to get
contemporary help if possible.  Conversion to postgres will work, and
what you are doing is a good idea.

merlin

Re: Create and access a dictionary type

[Rob Sargent]

>> 
>> Well, yes - you can always decompose the dict and store it in an EAV, but 
>> sometimes it's more convenient to just use "nested" dictionary at the row 
>> level. Which is what a JSONB column does.
> 
> Edgar Codd is rolling in his grave.
> 
> 
Contributing mightily to the global angular momentum, for sure.
> -- 
> Angular momentum makes the world go 'round.

Re: Create and access a dictionary type

[Ron]

On 1/5/22 10:35 AM, Tomas Vondra wrote:

On 1/5/22 17:24, Ron wrote:

On 1/5/22 7:22 AM, Tomas Vondra wrote:

On 1/5/22 14:17, Shaozhong SHI wrote:
Any examples in Postgres to create a dictionary type to store and 
access key value pairs?




I'd say JSONB can be used as an associative array, and e.g. in Python 
can map to dict data type.


The question confuses me.  Why isn't "create a table with Primary Key" 
the answer to "Any examples in Postgres to create a dictionary type to 
store and access key value pairs?"




Well, yes - you can always decompose the dict and store it in an EAV, but 
sometimes it's more convenient to just use "nested" dictionary at the row 
level. Which is what a JSONB column does.


Edgar Codd is rolling in his grave.


--
Angular momentum makes the world go 'round.

Re: Create and access a dictionary type

[Tomas Vondra]

On 1/5/22 14:36, Shaozhong SHI wrote:
I just checked operators.  I could not find any operator to set a new 
value given a key.




I'd say jsonb_set is what you're looking for.

Postgres does not have a thing that'd match the Oracle feature exactly, 
you'll have to adjust your application to use what's available.


regards


--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Create and access a dictionary type

[Tomas Vondra]

On 1/5/22 17:24, Ron wrote:

On 1/5/22 7:22 AM, Tomas Vondra wrote:

On 1/5/22 14:17, Shaozhong SHI wrote:
Any examples in Postgres to create a dictionary type to store and 
access key value pairs?




I'd say JSONB can be used as an associative array, and e.g. in Python 
can map to dict data type.


The question confuses me.  Why isn't "create a table with Primary Key" 
the answer to "Any examples in Postgres to create a dictionary type to 
store and access key value pairs?"




Well, yes - you can always decompose the dict and store it in an EAV, 
but sometimes it's more convenient to just use "nested" dictionary at 
the row level. Which is what a JSONB column does.


regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Create and access a dictionary type

[Ron]

On 1/5/22 7:22 AM, Tomas Vondra wrote:

On 1/5/22 14:17, Shaozhong SHI wrote:
Any examples in Postgres to create a dictionary type to store and access 
key value pairs?




I'd say JSONB can be used as an associative array, and e.g. in Python can 
map to dict data type.


The question confuses me.  Why isn't "create a table with Primary Key" the 
answer to "Any examples in Postgres to create a dictionary type to store and 
access key value pairs?"


--
Angular momentum makes the world go 'round.

Re: How best to create and use associative array type in Postgres?

[Tom Lane]

Shaozhong SHI  writes:
> What do you think this attempt by using create type and create a function?
> Managing Key/Value Pairs in PostgreSQL (justatheory.com)
> 

The date alone should suggest to you that there might be
better ways to do it by now.

regards, tom lane

Re: How best to create and use associative array type in Postgres?

[Shaozhong SHI]

What do you think this attempt by using create type and create a function?

Managing Key/Value Pairs in PostgreSQL (justatheory.com)


Regards,

David

On Wed, 5 Jan 2022 at 14:54, Tom Lane  wrote:

> Shaozhong SHI  writes:
> > How best to create and use associative array type in Postgres?
>
> I think the closest thing you'd find to that is jsonb, or
> contrib/hstore if you'd like something with a bit less complexity.
> The notation is unlikely to look much like Oracle, but they
> both have the ability to store sets of key/value pairs.
>
> https://www.postgresql.org/docs/current/datatype-json.html
> https://www.postgresql.org/docs/current/hstore.html
>
> regards, tom lane
>

Re: How best to create and use associative array type in Postgres?

[Tom Lane]

Shaozhong SHI  writes:
> How best to create and use associative array type in Postgres?

I think the closest thing you'd find to that is jsonb, or
contrib/hstore if you'd like something with a bit less complexity.
The notation is unlikely to look much like Oracle, but they
both have the ability to store sets of key/value pairs.

https://www.postgresql.org/docs/current/datatype-json.html
https://www.postgresql.org/docs/current/hstore.html

regards, tom lane

Replicate consistent snapshot to a second PG instance using FDWs

[Thomas Michael Engelke]

We have a setup where we run 2 locations with both locations containing 
a full HA setup (using Patroni, etcd, Consul). Each location has 2 PG 
servers + 1 witness node. At certain time intervals or on demand, our 
customer would want to send the data from one location to the other over 
the wire, either in full (overwriting the existing data) or selected 
tables/rows (incrementally).


To simplify the used technology I am thinking of implementing all of 
that in PostgreSQL itself; the alternative would be to code something, 
probably in Python, that executes pg_dump/pg_restore using configuration 
created specifically for the use case.


My idea is to execute everything in SQL, using fdw to create fdw tables 
for each table that needs to be transferred (on the target server B1):


DROP TABLE tablename;
CREATE TABLE tablename_fdw SERVER A1;
CREATE TABLE tablename LIKE tablename_fdw INCLUDING ALL;

Then just pull the data over:

INSERT INTO tablename SELECT * FROM tablename_fdw;

Please ignore obvious possible performance optimizations.

However, the copy of the data needs to be consistent, so I would need to 
have to copy a snapshot over. For a direct connection all is well 
understood, but working with remote tables using fdw here not all is 
well understood (by me).


I found a talk from PGCon 2020 from 2nd Quadrant where they state:

"Open a foreign transaction when FDW access the remote first time within 
the local transaction"
"Foreign transaction uses SERIALIZABLE when the local transaction has 
SERIALIZABLE, otherwise use REPEATABLE READ"
"This ensures that if a query performs multiple table scans on the 
remote server, it will get snapshot-consistent results for all the scans"


Is this what I am looking for? Can I reliable query one table after the 
other over multiple fdw tables from the same server and get snapshot 
consistency in the same way a connection to one instance would grant me?

Re: Create and access a dictionary type

[Shaozhong SHI]

I just checked operators.  I could not find any operator to set a new value
given a key.

Regards,

David

On Wed, 5 Jan 2022 at 13:22, Tomas Vondra 
wrote:

> On 1/5/22 14:17, Shaozhong SHI wrote:
> > Any examples in Postgres to create a dictionary type to store and access
> > key value pairs?
> >
>
> I'd say JSONB can be used as an associative array, and e.g. in Python
> can map to dict data type.
>
>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: Create and access a dictionary type

[Tomas Vondra]

On 1/5/22 14:17, Shaozhong SHI wrote:
Any examples in Postgres to create a dictionary type to store and access 
key value pairs?




I'd say JSONB can be used as an associative array, and e.g. in Python 
can map to dict data type.



regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Postgis - geography function

[Tomas Vondra]

On 1/5/22 09:48, Sameer TWAYANA wrote:

Hi Sir,

I need some help. I have been using the postgis extension for many 
years. Now in postgis version 3.2 using geom data in geography with 
ST_Length and ST_Distance function returns some weird results. Before I 
used 3.1.4, all was good. But I got different results in version 3.2. So 
can you suggest why I am getting different results in postgis version 3.2.


I really appreciate for your help. Thank you.



You haven't shown a lot of detail about the issue, so it's hard to say 
what's going on, unfortunately :-(


You'll need to show us some queries with expected/actual results (the 
simpler the better, of course). And provide info about the Postgres 
version, etc.


BTW if it's a problem related to PostGIS, maybe using a mailing list for 
that specific project [1] would yield better results.


[1] https://lists.osgeo.org/mailman/listinfo/postgis-users


regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Create and access a dictionary type

[Shaozhong SHI]

Any examples in Postgres to create a dictionary type to store and access
key value pairs?

Regards,

David

Postgis - geography function

[Sameer TWAYANA]

Hi Sir,

I need some help. I have been using the postgis extension for many years.
Now in postgis version 3.2 using geom data in geography with ST_Length and
ST_Distance function returns some weird results. Before I used 3.1.4, all
was good. But I got different results in version 3.2. So can you suggest
why I am getting different results in postgis version 3.2.

I really appreciate for your help. Thank you.

-- 

*Sameer Twayana*
Web Developer

M: +977-9841471358

E: sameertw...@gmail.com 

A: Kathmandu, Nepal


*www.linkedin.com/in/sameer-twayana-b45549a3/
*

How best to create and use associative array type in Postgres?

[Shaozhong SHI]

In Oracle, one can create and use associative array.  For instance,
TYPE FID_MEASURE IS TABLE OF NUMBER INDEX BY VARCHAR2(38);
NODES_WAITING FID_SET;

How best to create and use associative array type in Postgres?

Or, what is the best/most efficient equivalent in Postgres?

Regards,

David

Re: Can we use sql language to create a function in Postgres?

[hubert depesz lubaczewski]

On Wed, Jan 05, 2022 at 11:22:34AM +, Shaozhong SHI wrote:
> If so, can we use Oracle SQL script in DO block?
> 
> Does this mean that all Oracle SQL can be used in Postgres?

I highly doubt that.
But - I used oracle last time like 2 years ago, so the only thing I can
tell you: TRY.
instead of asking if "something vague wll work" just try. it will work,
or not work, but I can't guess what you have in this "oracle sql" and
whether this particular bit of sql will be cross-plaftorm enough to
execute (or even parse) in pg.

depesz

Re: Can we use sql language to create a function in Postgres?

[Shaozhong SHI]

Any online documentation or examples for using Oracle SQL in Postgres?

Regards,

David

On Wed, 5 Jan 2022 at 11:22, Shaozhong SHI  wrote:

> If so, can we use Oracle SQL script in DO block?
>
> Does this mean that all Oracle SQL can be used in Postgres?
>
> Regards,
>
> David
>
> On Wed, 5 Jan 2022 at 11:12, hubert depesz lubaczewski 
> wrote:
>
>> On Wed, Jan 05, 2022 at 11:04:34AM +, Shaozhong SHI wrote:
>> > I was given an Oracle script.  Can we use sql language to create a
>> function
>> > in Postgres?
>>
>> Sure:
>>
>> create function z() returns int4 language sql as $$
>> select 123;
>> $$;
>>
>> depesz
>>
>

Re: Can we use sql language to create a function in Postgres?

[Shaozhong SHI]

If so, can we use Oracle SQL script in DO block?

Does this mean that all Oracle SQL can be used in Postgres?

Regards,

David

On Wed, 5 Jan 2022 at 11:12, hubert depesz lubaczewski 
wrote:

> On Wed, Jan 05, 2022 at 11:04:34AM +, Shaozhong SHI wrote:
> > I was given an Oracle script.  Can we use sql language to create a
> function
> > in Postgres?
>
> Sure:
>
> create function z() returns int4 language sql as $$
> select 123;
> $$;
>
> depesz
>

Re: Can we use sql language to create a function in Postgres?

[hubert depesz lubaczewski]

On Wed, Jan 05, 2022 at 11:04:34AM +, Shaozhong SHI wrote:
> I was given an Oracle script.  Can we use sql language to create a function
> in Postgres?

Sure:

create function z() returns int4 language sql as $$
select 123;
$$;

depesz

Re: Can we use sql language to create a function in Postgres?

[Ray O'Donnell]

On 05/01/2022 11:08, Ray O'Donnell wrote:

On 05/01/2022 11:04, Shaozhong SHI wrote:
I was given an Oracle script.  Can we use sql language to create a 
function in Postgres?


Yes, absolutely:

create function my_function(...)
as
$$
   select.
$$
language sql;


Should have added the link to the docs:

https://www.postgresql.org/docs/14/server-programming.html

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Can we use sql language to create a function in Postgres?

[Ray O'Donnell]

On 05/01/2022 11:04, Shaozhong SHI wrote:
I was given an Oracle script.  Can we use sql language to create a 
function in Postgres?


Yes, absolutely:

create function my_function(...)
as
$$
  select.
$$
language sql;


Is this what you mean?


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Can we use sql language to create a function in Postgres?

[Shaozhong SHI]

I was given an Oracle script.  Can we use sql language to create a function
in Postgres?

Regards,

David