Thread: external table
I am currently writing my own database apps in foxpro/dos (since 1980). pg seems to be a good way to move on, but I see a couple of problems. All of the application programs seem to be c based. Learning c for business apps is a problem. Currently I use a lookup database for many kinds of applications that I create for my company. It seems that the sql database arrangement is to have 1 database with multiple related tables inside. I do not want to have to keep current multiple look-up tables (cities, counties, police depts, their phone numbers, etc.) in separate databases. Can I use, say a database with billing tables (transacions, etc.) and refer to an external lookup table? I might store a "G" in the city field, which would refer to "San Francisco", etc. tia, John
John R. Sowden wrote: > I am currently writing my own database apps in foxpro/dos (since 1980). > pg seems to be a good way to move on, but I see a couple of problems. > > All of the application programs seem to be c based. Learning c for > business apps is a problem. Why do you have to learn C to use a program that is written in C? Or do I misunderstand you? > Currently I use a lookup database for many kinds of applications that I > create for my company. It seems that the sql database arrangement is to > have 1 database with multiple related tables inside. I do not want to > have to keep current multiple look-up tables (cities, counties, police > depts, their phone numbers, etc.) in separate databases. Can I use, say > a database with billing tables (transacions, etc.) and refer to an > external lookup table? I might store a "G" in the city field, which > would refer to "San Francisco", etc. What is the problem with keeping the lookup table in the database? There is a foreign data wrapper (file_fdw) included with PostgreSQL as a "contrib module" that will allow to use a flat OS file like a table. If you modify the file while PostgreSQL uses it, bad things are likely to happen. If the file doesn't change, I would definitely COPY it into a table. Yours, Laurenz Albe
On 07/04/2014 02:47 AM, Albe Laurenz wrote: > John R. Sowden wrote: >> I am currently writing my own database apps in foxpro/dos (since 1980). >> pg seems to be a good way to move on, but I see a couple of problems. >> >> All of the application programs seem to be c based. Learning c for >> business apps is a problem. > > Why do you have to learn C to use a program that is written in C? > Or do I misunderstand you? > >> Currently I use a lookup database for many kinds of applications that I >> create for my company. It seems that the sql database arrangement is to >> have 1 database with multiple related tables inside. I do not want to >> have to keep current multiple look-up tables (cities, counties, police >> depts, their phone numbers, etc.) in separate databases. Can I use, say >> a database with billing tables (transacions, etc.) and refer to an >> external lookup table? I might store a "G" in the city field, which >> would refer to "San Francisco", etc. > > What is the problem with keeping the lookup table in the database? > > There is a foreign data wrapper (file_fdw) included with PostgreSQL > as a "contrib module" that will allow to use a flat OS file like a table. > If you modify the file while PostgreSQL uses it, bad things are likely to happen. > If the file doesn't change, I would definitely COPY it into a table. > > Yours, > Laurenz Albe > I write programs using the foxpro/dos language (I run them using ubuntu/dosemu). It seems that the languages that I must write my database applications in, using pg apis, are c based. In reading books on the issue, the quote that stands out in the first few pages is "if you understand c, then you won't have any problem learning ..." I bout the kernigan 7 ritchie book in the 80s and decided that that is ridiculous, unless I wanted to get a job writing software. My programs are not just a list of queries and input forms. One is an accounting program (GL) another is an AR/billing program, etc. re: the external lookup table, I assume that I will more all of my dbf data to pg, not maintain a foreign table (foreign to pg). I am wondering how to create queries, etc. relating a table that is not inside the connected database. I expect to have separate databases for gl, billing, dispatch, service call tracking. Now each of these are separate tables (.dbf files). These are not flat files, they are relational. John
John R. Sowden, 04.07.2014 12:11: > I write programs using the foxpro/dos language (I run them using > ubuntu/dosemu). It seems that the languages that I must write my > database applications in, using pg apis, are c based. No, that's not true. I'd say programs for Postgres are written in essentially all popular programming languages. Java via JDBC, Delphi/Lazarus via ODBC, VisualBasic via .Net or ODBC, C# via .Net You can even use OpenOffice Base as a fronted to connect to a Postgres database Thomas
John R. Sowden wrote: > I write programs using the foxpro/dos language (I run them using > ubuntu/dosemu). It seems that the languages that I must write my > database applications in, using pg apis, are c based. In reading books > on the issue, the quote that stands out in the first few pages is "if > you understand c, then you won't have any problem learning ..." I bout > the kernigan 7 ritchie book in the 80s and decided that that is > ridiculous, unless I wanted to get a job writing software. There are PostgreSQL APIs for a lot of languages, including Perl, Java, Python, .NET and many others. They are not part of the PostgreSQL distribution, they are other open source projects or commercial offerings. Here is a good list: http://www.postgresql.org/download/products/2-drivers-and-interfaces/ I doubt that anybody writes PostgreSQL applications in C. > My programs are not just a list of queries and input forms. One is an > accounting program (GL) another is an AR/billing program, etc. > > re: the external lookup table, I assume that I will more all of my dbf > data to pg, not maintain a foreign table (foreign to pg). I am wondering > how to create queries, etc. relating a table that is not inside the > connected database. I expect to have separate databases for gl, > billing, dispatch, service call tracking. Now each of these are > separate tables (.dbf files). These are not flat files, they are > relational. One PostgreSQL database can contain many tables. You can organize these in Schemas. I would advise that you keep all tables that are used by one application in one database. It is no problem if several applications access the same database. Since PostgreSQL 9.3 there is a foreign data wrapper (postgres_fdw) that allows to access tables in different databases, but you should have a really good reason for that, because it means a performance penalty, complicates the architecture and does not guarantee that modifications to both databases are atomic (i.e., it may happen that the transaction succeeds in one database and fails in the other). Yours, Laurenz Albe