Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2013
    Posts
    7

    Unhappy Unanswered: Function of schema in postgre

    Greeting to all...!
    Can anyone please give me knowledge about the function of Schema is postgresql?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Certainly! What would you like to know?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    7
    Since m new to postgresql, I got stuck on one problem. The problem is as follows:
    I want to create a separate Folder in postgresql itself, in such a manner that under that particular folder, respective tables should be formed.

    Eg: I have raster as well as vector data & I want to create vector data folder in which only vector data is saved. Similarly, for raster data, I want to create raster data folder such that only raster data should be saved.

    Please help me what to do, and how to do! Should I use different schemas?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    "Folder" isn't really a concept that is supported or used directly by PostgreSQL. What exactly do you mean by a "folder"?

    What kind of class are you taking? Is this a database class, software design, or something else?

    I need a bit more background to help you get started.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2013
    Posts
    9

  6. #6
    Join Date
    Apr 2013
    Posts
    7
    Actually my question was to how to store one kind of tables in a particular database?
    My problem is:
    I have a spatial database, n under that I want to create multiple databases which would store respective data... Like one database would store only vector data another database would store only raster data.

  7. #7
    Join Date
    Apr 2013
    Posts
    9
    Use schema then. Though theoretically it's a bad practice to separate data in such manner. Normally table name prefix is used.

    How many tables are you planning to have?

  8. #8
    Join Date
    Apr 2013
    Posts
    7
    tables are MANY!
    now please guide me how does FUNCTION under a schema works?

  9. #9
    Join Date
    Apr 2013
    Posts
    9
    Schema is a logical namespace, nothing more. This abstraction allows you to separate tables in groups without losing their relations. In lame terms it's a path or directory. You can't create multilevelled schema, only one level is permitted.

    Every postgresql object (table, view, function, sequence, etc.) has a namespace, actually it's just an object's property. All information about objects thei relations and attributes is physically saved in Catalogs/informational_schema/* (read only) as well as Catalogs/pg_catalog/* (it's sort of a predefined namespace with tables about your current database structure, and you can write in there).

    The default schema for user data is called "public", all objects with unspecified namespace fall in this schema. The syntax for custom namespace is myschema.myobjectname. As I said before: while the database objects stay inside the same database regardless of the namespace they belong to they can "see" all other objects, even if they belong to other namespaces. So when you define a function for ex. you just specify the schema in it's name myschema.myfunction, and it doesn't limit this function or its body to this schema, you just position the current function in some particular schema, that's all.

    Example:
    Code:
    CREATE OR REPLACE FUNCTION myschema1.create_other()
      RETURNS integer AS
    $BODY$
    BEGIN
    	CREATE TABLE myschema2.my_table_copy AS (select * from myschema3.some_table);
    	
    	RETURN 1;
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;

    Hope this helps.
    Last edited by akaine; 04-18-13 at 03:04.

  10. #10
    Join Date
    Apr 2013
    Posts
    7
    THAT WAS A GREAT HELP!!! Helped me to understand the basic concept of schema....
    Another problem I'm facing is with POSTGIS.. can you guide me through that too?

  11. #11
    Join Date
    Apr 2013
    Posts
    9
    I've never worked with PostGIS. All I know is that basically it's the same PostgreSQL but with additional GIS specific indexing and filter/query functions. So conceptually same rules apply. As for the specific functions I'd recommend consulting web resources for that.

  12. #12
    Join Date
    Apr 2013
    Posts
    7
    Thanku so much for the help!
    Will consult web for any further query regarding POSTGIS.

    cheers!!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •