PostgreSQL – How to Insert into View Through GUI in pgAdmin

pgadminpostgresql

I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In complete production, there will be some client app doing the inserts, but it is not ready now. A customer doesn't want to wait for the app, but also doesn't want to learn SQL just to insert few rows. Inserting the data through pgAdmin would be ideal. Unfortunately, pgAdmin shows a "new row" while inspecting data only for tables, not for views.

So is it possible to enable inserting into updateable views in pgAdmin? If not, is it possible in any other PostgreSQL GUI?

It is not just for this customer – we plan to offer the database without the client app as an opensource "demo", and we would like this demo to have some value for SQL-inept potencial customers too.

Best Answer

Sorry, no luck with pgAdmin. Per documentation:

Note that views can’t be edited and are read only by design; updatable views (using rules) are not supported at this time.

There is an item in the project's TODO list:

Updateable View wizard

Seems like nobody tackled that, yet. (Contributors welcome.)
There have been requests to introduce this feature in the past. You could email pgadmin-support@postgresql.org to document popular demand ...

For now, it seems like Navicat supports it.

Related Solutions

PostgreSQL – How to Insert File Data into a Bytea Column

as superuser:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

insert into my_table(bytea_data) select bytea_import('/my/file.name');

Postgresql – How to connect to remote PostgreSQL server

I suppose the simplest way is to let the tester use ssh to login to your server. Then the tester's execution environment is whatever your server exposes through the shell. (I presume that would include psql.)

As far as I know, all modern Unix derivatives install ssh by default. That would include Linux, Solaris, OS/X (Apple), and BSD. I believe that Windows does not ship with ssh, but Cygwin and PuTTY are commonly used to remedy that problem.

Best Answer

Related Solutions

PostgreSQL – How to Insert File Data into a Bytea Column

Postgresql – How to connect to remote PostgreSQL server

Related Question