Calling rest service from postgresql functions and stored proc

[mahesh.sp]

Hi,

Would like to know whether postgresql supports calling a rest service from
function or stored procedure.

Looking for Oracle to postgresql feasibility study.

Searched in Google but could not find any official document regarding the
same. Appreciate if inputs or reference links are shared or confirmed.


Thanks,
Mahesh

Re: Calling rest service from postgresql functions and stored proc

[Magnus Hagander]

On Thu, Feb 7, 2019 at 10:53 AM mahesh.sp  wrote:

> Hi,
>
> Would like to know whether postgresql supports calling a rest service from
> function or stored procedure.
>
> Looking for Oracle to postgresql feasibility study.
>
> Searched in Google but could not find any official document regarding the
> same. Appreciate if inputs or reference links are shared or confirmed.
>
>
Absolutely. Pick any language capable of doing it (e.g. python, perl, v8/js
etc) and it will work fine. Depending on what the rest api looks like it
might also be a good idea to use something like multicorn to create a
foreign data wrapper to access it, thereby not needing a function or
procedure at all.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Calling rest service from postgresql functions and stored proc

[Pavel Stehule]

Hi

čt 7. 2. 2019 v 10:53 odesílatel mahesh.sp  napsal:

> Hi,
>
> Would like to know whether postgresql supports calling a rest service from
> function or stored procedure.
>
> Looking for Oracle to postgresql feasibility study.
>
> Searched in Google but could not find any official document regarding the
> same. Appreciate if inputs or reference links are shared or confirmed.
>

you can do simply via untrusted languages or you can use this extension
https://github.com/pramsey/pgsql-http (better).

or don't do it. Usually it is not great idea (sometimes there can be a
exceptions)

Regards

Pavel



>
> Thanks,
> Mahesh
>

Re: Connection issue

[Maximilian Tyrtania]

Just for the record, updating to Mac OS 10.14.3 resolved the issue.
Thanks, Max

How to add a new psql command ?

[Quentin Di-Fant]

Hello,

I was wondering : is it possible to add directly a new psql command which
can be run anytime in our PostgreSQL interpreter like any other normal psql
commands ?

I explain myself : in the PostgreSQL interpreter, when the command "\h" or
"\help" is written in the shell, we can see all the commands we have at our
disposal. We can also of course write our proper functions, in python
scripts for example, and run them direclty in one of our databases. But I
was wondering if it was possible to add directly a command, like \copy,
\select,  For example, let's say I create a new command called \resume
which will call my own-made python script, and I would want this command to
be part of the list of commands we see when we type \h.

Is it even possible ? And if it's possible, how can I achieve that ?

Thanks for your attention and I thank you in advance for all the
informations you will possibly give me.

Cordially.

Re: How to add a new psql command ?

[Pavel Stehule]

Hi

čt 7. 2. 2019 v 11:40 odesílatel Quentin Di-Fant 
napsal:

> Hello,
>
> I was wondering : is it possible to add directly a new psql command which
> can be run anytime in our PostgreSQL interpreter like any other normal psql
> commands ?
>
> I explain myself : in the PostgreSQL interpreter, when the command "\h" or
> "\help" is written in the shell, we can see all the commands we have at our
> disposal. We can also of course write our proper functions, in python
> scripts for example, and run them direclty in one of our databases. But I
> was wondering if it was possible to add directly a command, like \copy,
> \select,  For example, let's say I create a new command called \resume
> which will call my own-made python script, and I would want this command to
> be part of the list of commands we see when we type \h.
>
> Is it even possible ? And if it's possible, how can I achieve that ?
>
> Thanks for your attention and I thank you in advance for all the
> informations you will possibly give me.
>

It is not possible. psql is not designed to be enhanced.

you can use psql variables for this purpose partially

postgres=# \set x 'select 1;'
postgres=# :x
┌──┐
│ ?column? │
╞══╡
│1 │
└──┘
(1 row)




> Cordially.
>

Re: How to add a new psql command ?

[Michel Pelletier]

You can also shell out to a command with \! and send data one way using a
combo of \gset and \setenv, then bundle up the whole "function" as a .sql
file you include when you want it with \i.  For example here's a snippet I
use to get the session pid, export it, then fire up another tmux pane
running gdb attached to my session and then continue:

select pg_backend_pid() pid \gset
\setenv PID :'pid'
\! tmux split-window -h
\! tmux send-keys 'gdb /usr/bin/postgres ' $PID  'C-m' 'cont' 'C-m'

This trick is also useful to export snapshot ids to subprocesses that need
read consistent views with each other.

-Michel


On Thu, Feb 7, 2019 at 3:03 AM Pavel Stehule 
wrote:

> Hi
>
> čt 7. 2. 2019 v 11:40 odesílatel Quentin Di-Fant 
> napsal:
>
>> Hello,
>>
>> I was wondering : is it possible to add directly a new psql command which
>> can be run anytime in our PostgreSQL interpreter like any other normal psql
>> commands ?
>>
>> I explain myself : in the PostgreSQL interpreter, when the command "\h"
>> or "\help" is written in the shell, we can see all the commands we have at
>> our disposal. We can also of course write our proper functions, in python
>> scripts for example, and run them direclty in one of our databases. But I
>> was wondering if it was possible to add directly a command, like \copy,
>> \select,  For example, let's say I create a new command called \resume
>> which will call my own-made python script, and I would want this command to
>> be part of the list of commands we see when we type \h.
>>
>> Is it even possible ? And if it's possible, how can I achieve that ?
>>
>> Thanks for your attention and I thank you in advance for all the
>> informations you will possibly give me.
>>
>
> It is not possible. psql is not designed to be enhanced.
>
> you can use psql variables for this purpose partially
>
> postgres=# \set x 'select 1;'
> postgres=# :x
> ┌──┐
> │ ?column? │
> ╞══╡
> │1 │
> └──┘
> (1 row)
>
>
>
>
>> Cordially.
>>
>

Re: Odd messages on reloading DB table

[Steve Wampler]

On 2/7/19 9:28 AM, Steve Wampler wrote:

With PostgreSQL 9.5.15, I ran:

    pg_dump -t targets -d atst.experimentdb >nT.db

then I ran:

    psql -h langley atst.experimentdb 
...

    ERROR:  relation "targets" already exists
    ALTER TABLE
    ERROR:  relation "targets" does not exist
    LINE 1: UPDATE targets SET time_stamp=new.time_stamp,   ...
    ^
    QUERY:  UPDATE targets SET time_stamp=new.time_stamp,    modified=new.modified, 
id=new.id,    targets=new.targets WHERE (id=new.id)

    CONTEXT:  PL/pgSQL function public.targets_insert_or_update() line 1 at SQL 
statement
    COPY targets, line 1: "2017-10-31 09:37:28.798152    t    az_el_target 
{"name":"AZ_EL_Targets","priority":10,"tags":["{\\"Tag.tag..."

    ERROR:  relation "id_id_targets" already exists
    ERROR:  relation "time_stamp_id_targets" already exists
    ERROR:  trigger "targets_trigger_insert" for relation "targets" already 
exists
ERROR:  trigger "targets_trigger_update" for relation "targets" already exists
REVOKE
REVOKE
GRANT
GRANT

Eh?  It looks like it worked (maybe), but why:

    (1) the table already exist and the immediately doesn't exist?
    (2) report ERROR on UPDATE when there are no UPDATES in the input file?


I take it back.  The content of the table was not restored to the values in 
nT.db afterall.
--
Steve Wampler -- swamp...@nso.edu
The gods that smiled on your birth are now laughing out loud.

Odd messages on reloading DB table

[Steve Wampler]

With PostgreSQL 9.5.15, I ran:

   pg_dump -t targets -d atst.experimentdb >nT.db

then I ran:

   psql -h langley atst.experimentdbQUERY:  UPDATE targets SET time_stamp=new.time_stamp,modified=new.modified, 
id=new.id,targets=new.targets WHERE (id=new.id)

   CONTEXT:  PL/pgSQL function public.targets_insert_or_update() line 1 at SQL 
statement
   COPY targets, line 1: "2017-10-31 09:37:28.798152	t	az_el_target	 
{"name":"AZ_EL_Targets","priority":10,"tags":["{\\"Tag.tag..."

   ERROR:  relation "id_id_targets" already exists
   ERROR:  relation "time_stamp_id_targets" already exists
   ERROR:  trigger "targets_trigger_insert" for relation "targets" already 
exists
ERROR:  trigger "targets_trigger_update" for relation "targets" already exists
REVOKE
REVOKE
GRANT
GRANT

Eh?  It looks like it worked (maybe), but why:

   (1) the table already exist and the immediately doesn't exist?
   (2) report ERROR on UPDATE when there are no UPDATES in the input file?

I see similar errors when using -Fc on the dump and pg_restore to read it back 
in,
   except, I use "-a" on both pg_dump and pg_restore so the error about the
   table already exising goes away [no CREATE TABLE anymore, of course).

Can someone explain what happened and how it can be fixed?

For reference, here are the first few lines of nT.db:
=
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.15
-- Dumped by pg_dump version 9.5.15

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: targets; Type: TABLE; Schema: public; Owner: atst
--

CREATE TABLE public.targets (
time_stamp timestamp without time zone NOT NULL,
modified boolean DEFAULT false,
id character varying(256) NOT NULL,
targets text,
marked boolean DEFAULT true,
collectable boolean DEFAULT false,
proposalid text
);


ALTER TABLE public.targets OWNER TO atst;

--
-- Data for Name: targets; Type: TABLE DATA; Schema: public; Owner: atst
--

COPY public.targets (time_stamp, modified, id, targets, marked, collectable, 
proposalid) FROM stdin;
===
--
Steve Wampler -- swamp...@nso.edu
The gods that smiled on your birth are now laughing out loud.

Re: Odd messages on reloading DB table

[David G. Johnston]

On Thursday, February 7, 2019, Steve Wampler  wrote:
>
>(1) the table already exist and the immediately doesn't exist?
>(2) report ERROR on UPDATE when there are no UPDATES in the input file


>

Most likely the first attempt was schema qualified and so found the
existing targets table while the second attempt was not schema qualified
and targets is not in the search path.

One guess I have is that triggers are involved here and those triggers need
to be more resiliant in face of the recent search_path security update.

David J.

Full text search parser dictionary

[Eugene Podshivalov]

Hi all,
I have tried utilizing the full text searh feature and works as charm,
except for one think which I would like to suggest to improve.

When a document is split into tokens the parser removes any whitespace or
punctuation charactures not otherwise recognized. But some punctuation
charactures such as a dot, slash and hyphen may be used to denote
abbreviations and removing them from a token may result in changing the
meaning of a word.

Here are some examples from the Russian language.
1. Parsing "р-н" returns two separate meaningless charactures but the
combination denotes "район" (a district).
2. Parsing "с/с" returns two identical charactures which are later
recognized as stop words but the combination donotes "сельсовет" (a
subdistrict)
3. Parsing "o." returns a single characture which is later recognized as a
stop word but the conbination denotes "остров" (an island).

It would be nice to have a possibility to assign a dictionary to the parser
to recognize such cases.

Cheers,
Eugene