اتصال به پایگاه داده (Connecting to a Database)

psql یک برنامه کلاینت معمولی PostgreSQL است. برای اتصال به یک پایگاه داده باید نام پایگاه داده هدف، نام میزبان و شماره پورت سرور، و نام کاربری پایگاه داده‌ای که می‌خواهید با آن متصل شوید را بدانید. می‌توان این پارامترها را از طریق گزینه‌های خط فرمان، یعنی به ترتیب -d، -h، -p و -U به psql اعلام کرد. اگر آرگومانی پیدا شود که متعلق به هیچ گزینه‌ای نباشد، به عنوان نام پایگاه داده (یا در صورتی که نام پایگاه داده از قبل مشخص شده باشد، به عنوان نام کاربر پایگاه داده) تفسیر خواهد شد. همه این گزینه‌ها اجباری نیستند؛ مقادیر پیش‌فرض مفیدی وجود دارند. اگر نام میزبان را نادیده بگیرید، psql از طریق یک سوکت دامنه یونیکس (Unix-domain socket) به سروری روی میزبان محلی، یا از طریق TCP/IP به localhost در ویندوز متصل خواهد شد. شماره پورت پیش‌فرض در زمان کامپایل تعیین می‌شود. از آنجا که سرور پایگاه داده از همان پیش‌فرض استفاده می‌کند، در بیشتر موارد نیازی به مشخص کردن پورت نخواهید داشت. نام کاربر پیش‌فرض پایگاه داده، همان نام کاربری سیستم‌عامل شما است. پس از تعیین نام کاربر پایگاه داده، از آن به عنوان نام پایگاه داده پیش‌فرض استفاده می‌شود. توجه داشته باشید که نمی‌توانید با هر نام کاربری پایگاه داده به هر پایگاه داده‌ای متصل شوید. مدیر پایگاه داده شما باید شما را از دسترسی‌هایتان مطلع کرده باشد.

هنگامی که مقادیر پیش‌فرض کاملاً مناسب نیستند، می‌توانید با تنظیم متغیرهای محیطی PGDATABASE، PGHOST، PGPORT و/یا PGUSER به مقادیر مناسب، از تایپ مکرر جلوگیری کنید. (برای متغیرهای محیطی بیشتر، بخش 32.15 را ببینید.) همچنین داشتن یک فایل ~/.pgpass برای جلوگیری از نیاز مداوم به تایپ گذرواژه‌ها سودمند است. برای اطلاعات بیشتر بخش 32.16 را ببینید.

یک راه جایگزین برای مشخص کردن پارامترهای اتصال، استفاده از یک رشته conninfo یا یک URI است که به جای نام پایگاه داده به کار می‌رود. این سازوکار کنترل بسیار گسترده‌ای بر اتصال به شما می‌دهد. برای مثال:

$ psql "service=myservice sslmode=require"
$ psql postgresql://dbmaster:5433/mydb?sslmode=require

به این روش همچنین می‌توانید همان‌طور که در بخش 32.18 شرح داده شده است، از LDAP برای جستجوی پارامترهای اتصال استفاده کنید. برای اطلاعات بیشتر درباره تمام گزینه‌های اتصال موجود، بخش 32.1.2 را ببینید.

اگر اتصال به هر دلیلی برقرار نشود (مانند مجوزهای ناکافی، اجرا نشدن سرور روی میزبان مورد نظر و غیره)، psql یک خطا را برمی‌گرداند و خاتمه می‌یابد.

اگر هر دو ورودی استاندارد و خروجی استاندارد یک ترمینال باشند، psql کدگذاری کلاینت را روی “auto” تنظیم می‌کند، که کدگذاری کلاینت مناسب را بر اساس تنظیمات محلی (متغیر محیطی LC_CTYPE در سیستم‌های یونیکس) تشخیص می‌دهد. اگر این مورد مطابق انتظار کار نکرد، می‌توان کدگذاری کلاینت را با استفاده از متغیر محیطی PGCLIENTENCODING لغو و بازنویسی کرد.

ورود دستورهای SQL (Entering SQL Commands)

در حالت عادی، psql یک اعلان (prompt) همراه با نام پایگاه داده‌ای که psql در حال حاضر به آن متصل است، و در ادامه رشته => ارائه می‌دهد. برای مثال:

$ psql testdb
psql (18.6)
Type "help" for help.
testdb=>

در خط اعلان، کاربر می‌تواند دستورهای SQL را وارد کند. به طور معمول، خطوط ورودی زمانی به سرور ارسال می‌شوند که یک نقطه-ویرگول پایان‌دهنده فرمان وارد شود. پایان خط باعث اتمام فرمان نمی‌شود. بنابراین می‌توان دستورها را برای وضوح بیشتر در چندین خط توزیع کرد. اگر فرمان ارسال شده و بدون خطا اجرا شود، نتایج فرمان بر روی صفحه نمایش داده می‌شوند.

اگر کاربران غیرقابل‌اعتماد به پایگاه داده‌ای دسترسی دارند که از الگوی استفاده امن از شِما (secure schema usage pattern) پیروی نکرده است، نشست خود را با حذف شِماهای با قابلیت نوشتن همگانی از search_path آغاز کنید. می‌توان options=-csearch_path= را به رشته اتصال اضافه کرد یا دستور SELECT pg_catalog.set_config('search_path', '', false) را پیش از سایر دستورهای SQL اجرا نمود. این نکته مختص psql نیست؛ بلکه در مورد هر رابطی برای اجرای دستورهای دلخواه SQL صدق می‌کند.

هر زمان که فرمانی اجرا شود، psql همچنین رویدادهای اعلان ناهمگام (asynchronous notification events) تولید شده توسط LISTEN و NOTIFY را بررسی می‌کند.

در حالی که کامنت‌های بلوکی سبک C برای پردازش و حذف به سرور ارسال می‌شوند، کامنت‌های استاندارد SQL توسط psql حذف می‌گردند.

متادستورها (Meta-Commands)

هر چیزی که در psql وارد کنید و با یک بک‌اسلش بدون علامت نقل‌قول آغاز شود، یک متادستور psql است که توسط خود psql پردازش می‌شود. این دستورها psql را برای امور مدیریتی یا اسکریپت‌نویسی کاربردی‌تر می‌کنند. متادستورها اغلب دستورهای اسلش یا بک‌اسلش نامیده می‌شوند.

قالب یک دستور psql عبارت است از بک‌اسلش، که بلافاصله با فعل فرمان، و سپس هرگونه آرگومان دنبال می‌شود. آرگومان‌ها با هر تعداد نویسه فاصله خالی از فعل فرمان و از یکدیگر جدا می‌شوند.

برای گنجاندن فاصله خالی در یک آرگومان می‌توانید آن را با نقل‌قول تکی محصور کنید. برای گنجاندن یک نقل‌قول تکی در یک آرگومان، دو نقل‌قول تکی متوالی درون متن محصور با نقل‌قول تکی بنویسید. هر آنچه درون نقل‌قول‌های تکی قرار دارد علاوه بر این مشمول جایگزینی‌های سبک C برای \n (خط جدید)، \t (تب)، \b (پس‌بر)، \r (بازگشت به ابتدای سطر)، \f (برگه‌شمار)، \digits (هشت‌هشتی)، و \xdigits (شانزده‌شانزدهی) می‌شود. یک بک‌اسلش قبل از هر نویسه دیگر در متن محصور با نقل‌قول تکی، آن نویسه را، هر چه که باشد، نقل‌قول (بی‌اثر) می‌کند.

اگر یک دونقطه بدون علامت نقل‌قول (:) همراه با نام یک متغیر psql درون یک آرگومان ظاهر شود، همان‌طور که در بخش درج متغیرها در SQL (SQL Interpolation) در زیر توضیح داده شده، با مقدار آن متغیر جایگزین می‌شود. قالب‌های :'variable_name' و :"variable_name" که در آنجا شرح داده شده‌اند نیز عمل می‌کنند. نحو :{?variable_name} امکان بررسی تعریف شدن یا نشدن یک متغیر را فراهم می‌کند. این عبارت با TRUE یا FALSE جایگزین می‌شود. فرار دادن (escape) دونقطه با یک بک‌اسلش، آن را از جایگزینی محافظت می‌کند.

درون یک آرگومان، متنی که در بک‌تیک (`) محصور شده است، به عنوان یک خط فرمان در نظر گرفته می‌شود که به شل ارسال می‌گردد. خروجی فرمان (با حذف هرگونه نویسه خط جدید در انتهای آن) جایگزین متن درون بک‌تیک می‌شود. درون متنی که در بک‌تیک محصور است، هیچ نقل‌قول ویژه یا پردازش دیگری رخ نمی‌دهد، به جز اینکه موارد :variable_name که در آن‌ها variable_name یک نام متغیر psql است با مقدار متغیر جایگزین می‌شوند. همچنین، موارد :'variable_name' با مقدار متغیر جایگزین می‌شوند که به شکل مناسبی نقل‌قول شده تا به یک آرگومان واحد در فرمان شل تبدیل شود. (شکل دوم تقریباً همیشه ترجیح داده می‌شود، مگر اینکه کاملاً مطمئن باشید چه چیزی در متغیر قرار دارد.) به دلیل اینکه نویسه‌های بازگشت به ابتدای سطر (carriage return) و سطر جدید (line feed) در همه پلتفرم‌ها نمی‌توانند به شکل امن نقل‌قول شوند، قالب :'variable_name' در صورت ظاهر شدن چنین نویسه‌هایی در مقدار، یک پیام خطا چاپ می‌کند و مقدار متغیر را جایگزین نمی‌نماید.

برخی از دستورها یک شناسه SQL (مانند نام جدول) را به عنوان آرگومان دریافت می‌کنند. این آرگومان‌ها از قواعد نحو SQL پیروی می‌کنند: حروف بدون نقل‌قول به حروف کوچک تبدیل می‌شوند، در حالی که نقل‌قول دوگانه (") حروف را از تبدیل بزرگ/کوچک محافظت می‌کند و امکان گنجاندن فاصله خالی را در شناسه فراهم می‌سازد. درون نقل‌قول دوگانه، دو نقل‌قول دوگانه جفت‌شده به یک نقل‌قول دوگانه منفرد در نام نهایی تقلیل می‌یابند. برای مثال، FOO"BAR"BAZ به صورت fooBARbaz تفسیر می‌شود، و "A weird"" name" تبدیل به A weird" name می‌گردد.

تجزیه آرگومان‌ها در انتهای خط، یا هنگامی که بک‌اسلش بدون نقل‌قول دیگری یافت شود، متوقف می‌شود. یک بک‌اسلش بدون نقل‌قول به عنوان شروع یک متادستور جدید تلقی می‌شود. دنباله ویژه \\ (دو بک‌اسلش) پایان آرگومان‌ها را نشان می‌دهد و به تجزیه دستورهای SQL (در صورت وجود) ادامه می‌دهد. به این ترتیب دستورهای SQL و psql را می‌توان آزادانه در یک خط ترکیب کرد. اما در هر صورت، آرگومان‌های یک متادستور نمی‌توانند فراتر از انتهای خط ادامه پیدا کنند.

بسیاری از متادستورها بر روی بافر پرس‌وجوی جاری (current query buffer) عمل می‌کنند. این صرفاً بافری است که هر متن دستور SQL را که تایپ شده اما هنوز برای اجرا به سرور ارسال نشده است، نگهداری می‌کند. این شامل خطوط ورودی قبلی و همچنین هر متنی است که پیش از متادستور در همان خط قرار دارد.

بسیاری از متادستورها همچنین اجازه می‌دهند که x به عنوان یک گزینه به آن‌ها اضافه شود. این باعث می‌شود که نتایج در حالت گسترده (expanded mode) نمایش داده شوند، درست مانند اینکه از \x یا \pset expanded استفاده شده باشد..PP متا‌دستورهای زیر تعریف شده‌اند:

\a

\bind [ parameter ] ...

INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g

\bind_named statement_name [ parameter ] ...

INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
\bind_named stmt1 'first value' 'second value' \g

\c یا \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]

=> \c mydb myuser host.dom 6432
=> \c service=foo
=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
=> \c -reuse-previous=on sslmode=require    -- changes only sslmode
=> \c postgresql://tom@localhost/mydb?application_name=myapp

\C [ title ]

\cd [ directory ]

\close_prepared prepared_statement_name

SELECT $1 \parse stmt1
\close_prepared stmt1

\conninfo

\copy { table [ ( column_list ) ] } from { 'filename' | program 'command' | stdin | pstdin } [ [ with ] ( option [, ...] ) ] [ where condition ]
\copy { table [ ( column_list ) ] | ( query ) } to { 'filename' | program 'command' | stdout | pstdout } [ [ with ] ( option [, ...] ) ]

=> \echo `date`
Tue Oct 26 21:40:57 CEST 1999

=> \getenv home HOME
=> \echo :home
/home/postgres

=> SELECT format('create index on my_table(%I)', attname)
-> FROM pg_attribute
-> WHERE attrelid = 'my_table'::regclass AND attnum > 0
-> ORDER BY attnum
-> \gexec
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

=> SELECT 'hello' AS var1, 10 AS var2
-> \gset
=> \echo :var1 :var2
hello 10

=> SELECT 'hello' AS var1, 10 AS var2
-> \gset result_
=> \echo :result_var1 :result_var2
hello 10

-- check for the existence of two separate records in the database and store
-- the results in separate psql variables
SELECT
    EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,
    EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
\gset
\if :is_customer
    SELECT * FROM customer WHERE customer_id = 123;
\elif :is_employee
    \echo 'is not a customer but is an employee'
    SELECT * FROM employee WHERE employee_id = 456;
\else
    \if yes
        \echo 'not a customer or employee'
    \else
        \echo 'this will never print'
    \endif
\endif

foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
lo_import 152801

SELECT $1 \parse stmt1

testdb=> \setenv PAGER less
testdb=> \setenv LESS -imx4F

\startpipeline
SELECT * FROM pg_class;
SELECT 1 \bind \sendpipeline
\flushrequest
\getresults
\endpipeline

select 1; select 2; select 3;

select 1\; select 2\; select 3;

ویژگی‌های پیشرفته (Advanced Features)

متغیرها (Variables)

testdb=> \set foo bar

testdb=> \echo :foo
bar

\set HISTFILE ~/.psql_history-:DBNAME

testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;

testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";

testdb=> \set content `cat my_file.txt`
testdb=> INSERT INTO my_table VALUES (:'content');

testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '

$if psql
set disable-completion on
$endif

PSQL_EDITOR_LINENUMBER_ARG='+'
PSQL_EDITOR_LINENUMBER_ARG='--line '